Temp Table Scope

Temporary table scope is actually a little trickier than it might seem. First of all, Microsoft guidance is not particularly clear. Regarding local temp tables, BOL states: “[T]hey are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.”

Well, that’s not entirely accurate. “Connection” is misleading. Take a look at the following to see what I mean.

In the first run-through we’ll create a temp table the “normal” way; as you’ll see, everything works as expected.

-- If the temp table we intend to create already exists, drop it
IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL
    DROP TABLE #tbl;

-- Create a temp table
CREATE TABLE #tbl (ID INT);

-- Confirm the temp table was created
SELECT 'ObjectID' = OBJECT_ID(N'tempdb..#tbl');

-- Drop the temp table we created
IF OBJECT_ID(N'tempdb..#tbl') IS NOT NULL
    DROP TABLE #tbl;

-- Confirm the temp table was dropped
SELECT 'ObjectID' = OBJECT_ID(N'tempdb..#tbl');

So far so good. Now, let’s try the same thing again, but this time we’ll run the SQL from within an EXECUTE command.

-- Declare variables
DECLARE @nstrSQL NVARCHAR(MAX);

-- SQL to create the temp table and confirm it exists
SET @nstrSQL = N'CREATE TABLE #tbl (ID INT);
SELECT ''ObjectID'' = OBJECT_ID(N''tempdb..#tbl'');'
;

-- Create the temp table and confirm creation
EXECUTE (@nstrSQL);

-- See if the temp table still exists
SELECT 'ObjectID' = OBJECT_ID(N'tempdb..#tbl');

What?! Within the EXECUTE statement context, the temp table was created and we confirmed that it existed in that context. However, outside the scope of the EXECUTE statement, the table is gone. Yet we are still in the same connection — which you can confirm by looking at the process id inside and outside of the EXECUTE statement context (simply add @@SPID to the SELECT statements).

Thankfully, the global temp table resolves that issue, so it’s not too much of an inconvenience. Except…

With a global temp table you have to be a little more careful about execution process overlap. In other words, if there is the potential for multiple processes to run the same procedure at close to the same time, and that procedure creates a temporary table of the same name, then the processes could collide and one process might use the table while the other process is still using it, potentially causing data corruption, or worse.

Well, that’s easy enough to mitigate as well. We just need to make the temp table name effectively unique such that each process will have a temp table of a different name. And that is easy to do with a GUID. When we create the global temp table, we’ll simply add a GUID to the name. That is sufficient to solve the unique name problem, but it does add the complication of having to share the GUID with any other process that might need to use the temp table. But, if a temp table is necessary, then passing an additional variable for the table name is likely a small price to pay to ensure data quality.

-- Declare variables
DECLARE
    @nstrGUID NCHAR(36) = NEWID()
    , @nstrSQL NVARCHAR(MAX)
;

-- SQL to create the temp table and confirm it exists
SET @nstrSQL = N'CREATE TABLE [##tbl_' + @nstrGUID + '] (ID INT);
SELECT ''ObjectID'' = OBJECT_ID(N''tempdb..[##tbl_' + @nstrGUID + ']'');'
;

-- Create the temp table and confirm creation
EXECUTE (@nstrSQL);

-- SQL to re-confirm the temp table exists
SET @nstrSQL = N'SELECT ''ObjectID'' = OBJECT_ID(N''tempdb..[##tbl_'
    + @nstrGUID + ']'');'
;

-- See if the temp table still exists
EXECUTE (@nstrSQL);

Problem solved.