Temp Tables – Scoping & Eclipsing
Introduction
In this post I am going to take a closer look at the scoping rules of temp tables and other temporary objects. Questions that will be addressed are:
- What is the scope of a temp table?
- Can a temp table be seen in a sub-procedure?
- Can a temp table be created in a sub-procedure?
- How are naming conflicts between temp tables in sub-procedures handled?
Sessions & Batches
The first temp table scope is the session. If a temp table is created in a session it will live as long as that session is connected. Following batches and requests can still see and access the temp table.
The following example demonstrates that:
INSERT INTO #TempTable(Id) VALUES(1),(2),(3);
SELECT Id FROM #TempTable;
GO -- New Batch
SELECT Id FROM #TempTable;
[/sql]
SSMS splits scripts that contain a GO into separate batches that are submitted to SQL Server one after the other on the same connection. The effect is the same as highlighting the code before the GO and submitting it, followed by highlighting the code after the GO and submitting that. The output you will get is going to look similar to this:
The second result set proves that the temp table was still visible in the second batch.
If you try to rerun the script, you will get this error message:
However, if you interrupt the connection, the temp table will be dropped and will not be accessible anymore, even if you end up with the same SPID after the reconnect.
The Temp Table Name Suffix
To make a script like the above re-runnable, you have to drop the temp table either at the end or at the beginning of the script. At the beginning of the script the drop can only be executed if the table exists. To figure out if the able exists you could have a look into tempdb.sys.objects. However, as you can see in the next image, the name in there is not exactly #TempTable. Instead it has a very long suffix like this:
The actual object name is build out of the name specified when creating the object at the beginning, an up to twelve digit long hexadecimal number and in between enough underscores to fill the name to 128 characters.
That suffix allows SQL Server to distinguish between temporary objects in different scopes that have the same name. Because of the suffix the maximum name length for temporary objects is restricted. As the following script shows, you can put up to 128 characters in the name of a non-temporary object. However, only 116 characters are allowed in the name of a temporary object.
GO
CREATE PROCEDURE #TestObjectNameLength
@NameLength INT,
@TempTable BIT
AS
BEGIN
DECLARE @TblName NVARCHAR(MAX) = '';
DECLARE @i INT = 1;
WHILE(@i<14)
BEGIN
SET @TblName += 'aaaaBcccc'+RIGHT(STR(@i),1);
SET @i += 1;
END
SET @TblName = LEFT(
CASE WHEN @TempTable = 1 THEN '#' ELSE '' END + @TblName,
@NameLength
);
DECLARE @Cmd NVARCHAR(MAX) = 'CREATE TABLE '+(@TblName)+'(i INT);';
--DECLARE @Cmd NVARCHAR(MAX) = 'CREATE PROCEDURE '+(@TblName)+' AS RETURN 0;';
DECLARE @Msg NVARCHAR(MAX) = 'Success';
BEGIN TRAN;
BEGIN TRY
EXEC(@Cmd);
END TRY
BEGIN CATCH
SET @Msg = ERROR_MESSAGE();
END CATCH
ROLLBACK;
SELECT @TempTable TempTable,(@TblName) TblName,LEN(@TblName) Length,@Msg Outcome;
END;
GO
EXEC #TestObjectNameLength 129,0;
EXEC #TestObjectNameLength 128,0;
EXEC #TestObjectNameLength 117,1;
EXEC #TestObjectNameLength 116,1;
[/sql]
The procedure takes two parameters. The first one is the length of the name; the second specifies whether it should create a temporary object (1) or a standard object (0). It generates a name of the specified length and creates the object. The output contains 4 columns. The first reflects whether the object was temporary or not. The second and third columns show the generated name and its length. The forth column contains either the word "success" or the error that happened during the attempt to create the object.
The returned error messages clearly confirm the maximum name length of 128 for standard objects and 116 for temporary objects.
Dropping a Temporary Object
We have seen that we cannot use the sys.objects catalog view to see if a temporary object of a given name exists in the current scope. The reason is that the mapping between the name suffix and the scope is done internally and is not exposed through a DMV.
However, we can use the OBJECT_ID() function. The following query returns a non-NULL value, if a #TempTable exists in the current scope:
[/sql]
Important is here to specify the database name followed by two dots before the name of the temp table itself. This is contrary to the standard name resolution rules in SQL Server. As the following image shows, SQL Server usually ignores the database name when accessing a temp table and even warns about that fact:
So, to conditionally drop a temp table you would have to execute the following code:
[/sql]
Hidden Treasures
If you are now thinking that you could use the full names of a temp table after retrieving it from tempdb.sys.objects to peek into the contents of temp tables that live in other scopes, I have to disappoint you:
As soon as SQL Server sees a # character at the beginning of an object name, it uses the temp object name resolution method. That means, it ignores any given database name and then tries to find a temporary table with that name (including the underscore padding and the number at the end) in the current scope. As that cannot exist — after all it is longer than the allowed 116 characters — the statement fails.
Temporary Procedures
Similar to temp tables, SQL Server allows the creation of temp procedures:
For temp procedures the same name resolution and scoping rules apply as for temp tables. As this is a post about temp objects, most examples are using temporary procedures. However, everything shown hereafter works the same way inside normal procedures.
SQL Server currently does not support other types of temporary objects. So while for example a temporary view could be extremely helpful in some cases, sadly you are stuck with only temporary tables and procedures.
Procedure Scope
If you create a temp table inside a stored procedure its scope is restricted to the current execution of that procedure. Once the execution exits out of the procedure, the temp table will be automatically dropped:
GO
CREATE PROCEDURE #TempProc
AS
BEGIN
CREATE TABLE #TempTable(i INT);
SELECT CASE WHEN OBJECT_ID('tempdb..#TempTable') IS NOT NULL THEN '#TempTable exists' ELSE '#TempTable gone' END AS Status;
END;
GO
EXEC #TempProc;
SELECT CASE WHEN OBJECT_ID('tempdb..#TempTable') IS NOT NULL THEN '#TempTable exists' ELSE '#TempTable gone' END AS Status;
[/sql]
That means that you cannot call a procedure to create a temp table for you to use later on.
To Drop or Not To Drop
When a temporary object goes out of scope at the end of a procedure execution, it actually does not get dropped — at least not completely.
The following example creates a #TempTable inside a procedure and inserts 10 rows. The second column is a fixed length character field that causes each row to fill a page. The next statement gets the OBJECT_ID of the #TempTable and stores it in the #object_id table. The select statement at the end of the procedure returns the name of the temporary object with that OBJECT_ID together with its creation time and the number of pages it is taking up in the database. There are two page counts. The used_page_count reflects the number of pages in use by the table. The reserved_page_count might be higher because table space is reserved in steps of 8 pages after the first 8 single pages where added to the table.
IF OBJECT_ID('tempdb..#object_id') IS NOT NULL DROP TABLE #object_id;
CREATE TABLE #object_id(object_id INT);
GO
CREATE PROCEDURE #TempProc
AS
BEGIN
CREATE TABLE #TempTable(i INT IDENTITY(1,1), d CHAR(8000) DEFAULT '*');
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #TempTable DEFAULT VALUES;
INSERT INTO #object_id(object_id) SELECT OBJECT_ID('tempdb..#TempTable');
SELECT t.object_id,t.name,t.type_desc,t.create_date,s.used_page_count,s.reserved_page_count
FROM tempdb.sys.tables t
JOIN #object_id o
ON t.object_id = o.object_id
JOIN tempdb.sys.dm_db_partition_stats s
ON o.object_id = s.object_id;
END;
GO
EXEC #TempProc;
SELECT t.object_id,t.name,t.type_desc,t.create_date,s.used_page_count,s.reserved_page_count
FROM tempdb.sys.tables t
JOIN #object_id o
ON t.object_id = o.object_id
JOIN tempdb.sys.dm_db_partition_stats s
ON o.object_id = s.object_id;
[/sql]
The same select statement is repeated at the end of the script after the procedure was executed. The following image shows the output after executing the script.
After the procedure exits, there is still a temporary object with the same OBJECT_ID and the same creation time. However it has a different name and instead of 10 used pages only 2. The reason for this behavior is that SQL Server keeps the first two pages of a temporary table that was created inside a procedure around to make the next execution of this procedure a little cheaper.
Creating temporary objects at a high rate is actually quite expensive because each time you have to reserve at least two pages and record their use in the GAM and SGAM pages of the database. Those pages quickly turn into a major bottleneck in tempdb (read: PageLatch contention). To alleviate this, SQL Server tries to reuse the first two pages of any in-procedure-temp-table.
For SQL Server to be able to do this, the temp table and the surrounding code need to adhere to a few requirements. I won't go into more detail here, but as long as you don't do anything to a temp table that you couldn't do to table variable you are safe. For more details check out “Temporary Table Caching Explained” by Paul White.
The name that the table stub takes is the hexadecimal representation of its OBJECT_ID. The link between these stubs and the calling procedure is maintained internally and again is not exposed in any DMVs.
The Outside Scope
We have seen that a temporary table does not survive the procedure that it was created in. On the other hand, if you create a temp table and then call a procedure on the same connection, the temp table is visible and accessible. The procedure can select from as well as alter the data in the table:
IF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;
GO
CREATE PROCEDURE #TempProc
AS
BEGIN
INSERT INTO #TempTable VALUES(13);
DELETE FROM #TempTable WHERE i = 42;
END;
GO
CREATE TABLE #TempTable(i INT);
INSERT INTO #TempTable VALUES(42);
EXEC #TempProc;
SELECT * FROM #TempTable;
[/sql]
You could even alter the schema of the temp table inside the procedure and for example add an additional column to it:
IF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;
GO
CREATE PROCEDURE #TempProc
AS
BEGIN
ALTER TABLE #TempTable ADD d INT NOT NULL DEFAULT CHECKSUM(NEWID());
END;
GO
CREATE TABLE #TempTable(i INT);
INSERT INTO #TempTable VALUES(42);
EXEC #TempProc;
SELECT * FROM #TempTable;
[/sql]
So while you can't use a sub procedure to create a temp table, you could create a temp table stub and then use a sub procedure to alter its schema to your needs. But that certainly would not be a good design pattern to follow.
Lost Temp Tables
Besides of altering the data or even the schema of a temp table that was created outside the current procedure, you can even drop that table. This is particularly dangerous if you create a temp table only on some of the path ways through the procedure, but have a general clean-up section at the end were you drop the temp table if it exist. Code like that can end up inadvertently dropping an object that it did not create itself.
The Big Temp Table Eclipse
If you write a stored procedure and create a temp table in there for your own use you should be able to assume that this procedure will execute no matter what. This is certainly the case if you use table variables instead of temp tables. But even with temp table your code is guaranteed to execute successfully.
If you are executing a stored procedure, it can as we have seen access preexisting temp tables. It can also create a temp table with the same name. When that happens the outside temp object goes temporarily out of scope. If you end up dropping the inner temp table, the outer one comes back into scope. This is demonstrated in the following code snippet:
IF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;
GO
CREATE PROCEDURE #TempProc
AS
BEGIN
SELECT 'before inner create' AS [time],* FROM #TempTable;
CREATE TABLE #TempTable(InnerTbl INT);
INSERT INTO #TempTable VALUES(76);
SELECT 'after inner create' AS [time],* FROM #TempTable;
DROP TABLE #TempTable;
SELECT 'after inner drop' AS [time],* FROM #TempTable;
END;
GO
CREATE TABLE #TempTable(OuterTbl INT);
INSERT INTO #TempTable VALUES(42);
EXEC #TempProc;
[/sql]
This script creates a temp table #TempTable with one column OuterTbl. It then calls the procedure that first selects from that #TempTable. Afterwards it creates a new temp table with the same name but a different column name: InnerTbl. After selecting from that table it drops it and afterwards selects from #TempTable again. The output looks like this:
So, inside of a stored procedure it is possible to temporarily eclipse a preexisting temporary object.
Exam question: Can you think of a situation where this line of T-SQL code will execute without error:
[/sql]
Other Temp Table Scopes
A stored procedure is not the only scoping frame for temp objects. The same rules apply to dynamically executed T-SQL as well. Any temporary object that is created inside a EXEC('...'); construct is only visible inside thereof. That means – as with procedures – you cannot use dynamic SQL to create a temp table for later use. On the other hand it is possible to access any preexisting temporary objects inside of dynamic SQL code.
Summary
This article took an in-depth look at the scoping rules of temporary objects. We looked at three different scopes: The connection, the procedure and dynamic T-SQL statements. We saw how SQL Server manages for multiple temporary objects with the same name to exist in different scopes at the same time. And we discovered that it is possible for one temp object to temporarily eclipse another one.
One of the more surprising discoveries was that SQL Server does keep the first few pages of a temp table around for reuse in some circumstances. This potentially significantly reduces page latch contention in tempdb.
Newsletter Sign Up
2 Responses to Temp Tables – Scoping & Eclipsing
Leave a Reply
You must be logged in to post a comment.
We know how to make a database fast. We’ll help you do the same.
From the sqlity.net blog
-
How to identify the SQL Server Service Account in T-SQL
-
How to Change a SQL Login’s Password
-
Hash Algorithms – How does SQL Server store Passwords?
-
How to Re-Create a Login with only a Hashed Password
-
B+Trees – How SQL Server Indexes are Stored on Disk
-
The Hidden SQL Server Gem: UPDATE from SELECT
-
A Join A Day – Nested Joins
-
Determine the Uptime of a SQL Server Instance
-
A Join A Day – The Left Anti Semi Join
-
Log Reuse Waits Explained: LOG_BACKUP
Pingback: using temporary tables inside if block with a procedureJust a Day in the Office | Just a Day in the Office
Pingback: Can A Temp Table Name Cause Plan Cache Bloat? - sqlity.net