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.