Sql-server – Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan

It seems that this behavior / ability is both known and deprecated. I was looking through the sys.dm_os_performance_counters DMV the other day and noticed the following two entries:

object_name                      counter_name    instance_name
-----------------------------    ------------    -------------------------------
SQLServer:Deprecated Features    Usage           '@' and names that start with '@@' as
                                                 Transact-SQL identifiers

SQLServer:Deprecated Features    Usage           '#' and '##' as the name of temporary
                                                 tables and stored procedures

I then checked the MSDN documentation and found both of these noted on the Deprecated Database Engine Features in SQL Server 2016 page, in the Features Not Supported in a Future Version of SQL Server section, under the Category of "Transact-SQL":

Deprecated feature                     Replacement                               Feature ID
-----------------------------------    --------------------------------------    ----------
Use of #, ## as temporary table and    Use at least one additional character.    185
temporary stored procedure names.

Use of @, @@, or @@ as                 Do not use @ or @@ or names that begin    186
Transact-SQL identifiers.              with @@ as identifiers.

The earliest reference I could find for this deprecation notice was in the SQL Server 2008 documentation. And while the version drop-down on that "Deprecated Database Engine Features" page does not have an entry for SQL Server 2005, you can still get to that documentation via, https://msdn.microsoft.com/en-us/library/ms143729(v=sql.90).aspx, and see that neither of these items are listed.

That information leads me to the following conclusions:

• Question Part 1 (is this a bug or intended behavior):

  It is intended, albeit undesirable, behavior.

• Question Part 2 (is there a benefit to this behavior):

  Not only does there not seem to be any beneficial use-case here, even if there was, it wouldn't matter too much due to this behavior / ability being deprecated and hence not something that should be used going forward, especially in new code / projects.

In SQL Server 2000 the #temp table version does fail with the message

Invalid object name '#temp'.

However since SQL Server 2005 compiling a plan with a local temporary table does actually create the temporary table behind the scenes.

You can see this by getting the estimated plan for

Create table #temp
(
ID INT IDENTITY (1,1) 
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

DROP TABLE #temp

Which produces an output like

The negative object id is the id of the object fleetingly created. Polling tempdb.sys.tables in a loop whilst generating the estimated plan and comparing with the TableID subsequently output can show this.

The estimated plan you see for the statements referencing the temp table is of limited use however as even the addition of a single row to the temp table will trigger a statement level recompile and potentially a different plan when you actually execute the SQL.

This behaviour does not apply to global temporary tables or permanent tables.

I presume this is also the same reason as

/*Works fine (assuming T doesn't exist)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   T
ELSE
  SELECT 1 AS X
  INTO   T

/*Fails (There is already an object named '#T' in the database.)*/   
IF 1 = 1
  SELECT 1 AS X
  INTO   #T
ELSE
  SELECT 1 AS X
  INTO   #T