The Life & Times of a SQL Temp Table

Code from Philly.NET code camp 2009.2 Saturday October 17, 2009

The first code block illustrates the scope of a temp table within dynamic T-SQL execution.  In short if you create a temp table any dynamic SQL executions within the same connection are in scope the scope of a temp table.

It also is a simple demonstration of the connection independence of temp tables.  Open two windows in SSMS connected to the same database.  Paste this code in both windows and execute multiple times each. 

Things to Notice:

• The Object_ID values in each window is always different
• If the final select statement is executed over and over it will always have the same values
  • Try Executing one window then the other and coming back to the first to just execute the final select.

--Duplicate content and run in another window

IF OBJECT_ID('tempdb..#MyTemp') IS NOT NULL 
    DROP TABLE #MyTemp


CREATE TABLE #MyTemp (
    SomeValue    INT NOT NULL
)

DECLARE @StartingNumber    INT


SELECT @StartingNumber = CAST(RAND()*1000 AS INT)


INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 1 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 2 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + @StartingNumber )


-- This would work if it were another module like a sproc or a udf
-- even nested calls
EXEC (N'INSERT INTO #MyTemp ( SomeValue )VALUES ( 200 );
INSERT INTO #MyTemp ( SomeValue )VALUES ( 12356 );')


SELECT 
      * 
    , OBJECT_ID('tempdb..#MyTemp') AS [#MyTemp OBJECT_ID]
FROM 
    #MyTemp AS mt

Alas the following code does not work. This example demonstrates that a temp table created in a dynamically executed statement is not in scope of the code that called the dynamic code.  The @SQLStatement variable contains the exact code of the previous example with the single tic (‘) escaped.  The select at the bottom shows that the table does not exist. If you should run it and it does have a result it is possible you did what I did in the presentation and executed in the same window as the previous example without dropping the temp table first.  If this should happen just drop the table and try again.  It really does work at showing that this technique does not work.

-------------------------------------------------------------------------------
-- Sadly this does not work
-------------------------------------------------------------------------------
-- Same code as above but executed dynamically
-- Tem table only exists during EXEC call
DECLARE @SQLStatement    VARCHAR(max)

SET @SQLStatement = '
IF OBJECT_ID(''tempdb..#MyTemp'') IS NOT NULL 
    DROP TABLE #MyTemp


CREATE TABLE #MyTemp (
    SomeValue    INT NOT NULL
)

DECLARE @StartingNumber    INT


SELECT @StartingNumber = CAST(RAND()*1000 AS INT)


INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 1 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + 2 )
INSERT INTO #MyTemp ( SomeValue )VALUES ( @StartingNumber + @StartingNumber )


-- This would work if it were another module like a sproc or a udf
-- even nested calls
EXEC (N''INSERT INTO #MyTemp ( SomeValue )VALUES ( 200 );
INSERT INTO #MyTemp ( SomeValue )VALUES ( 12356 );'')


SELECT 
      * 
    , OBJECT_ID(''tempdb..#MyTemp'') AS [#MyTemp OBJECT_ID]
FROM 
    #MyTemp AS mt

'

EXEC (@SQLStatement)

SELECT OBJECT_ID('tempdb..#MyTemp') AS [#MyTemp OBJECT_ID]