SQL Server – Estimated Plan Generation Succeeds for Batch Creating and Using #temp Table but Fails with Permanent Table

execution-plansql server

In reviewing the estimated execution plan creation process, I came across this section in SQL Server Execution plans by Grant Fritchey, He mentions:

The optimizer, which is what generates estimated execution plans,
doesn't execute T-SQL. It does run the statements through the
algebrizer…that is responsible for
verifying the names of database objects. Since SQL Server has not yet
executed the query, the temporary table does not yet exist. This is
the cause of the error. Running this same bit of code through the
actual execution plan will work perfectly.

In the book he references this code:

CREATE TABLE TempTable
(
Id INT IDENTITY(1, 1) 
);

INSERT INTO TempTable
DEFAULT VALUES

SELECT *
FROM TempTable;

and the Error you will receive is

Msg 208, Level 16, State 1, Line 7 Invalid object name 'TempTable'.

To me, this wasn't a temporary table but instead an actual table. So when I tried with a temporary "#" table. I found that the estimated execution plan generated without issue. The estimated plan that was generated is shown here.

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

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp

Can someone explain why there is this difference? Shouldn't the #table not be generated?

Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.

Best Answer

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

enter image description here

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
Related Question