{"id":1109,"date":"2012-10-16T10:00:51","date_gmt":"2012-10-16T14:00:51","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1109"},"modified":"2014-11-13T13:54:22","modified_gmt":"2014-11-13T18:54:22","slug":"temp-tables-scoping-eclipsing","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/","title":{"rendered":"Temp Tables \u2013 Scoping &amp; Eclipsing"},"content":{"rendered":"<div>\n<h3>Introduction<\/h3>\n<p>\nIn 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:<\/p>\n<ul>\n<li>What is the scope of a temp table?<\/li>\n<li>Can a temp table be seen in a sub-procedure?<\/li>\n<li>Can a temp table be created in a sub-procedure?<\/li>\n<li>How are naming conflicts between temp tables in sub-procedures handled?<\/li>\n<\/ul>\n<h3>Sessions &amp; Batches<\/h3>\n<p>\nThe 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.\n<\/p>\n<p>\nThe following example demonstrates that:\n<\/p>\n<div>\n[sql]\nCREATE TABLE #TempTable(Id INT);<br \/>\nINSERT INTO #TempTable(Id) VALUES(1),(2),(3);<br \/>\nSELECT Id FROM #TempTable;<\/p>\n<p>GO -- New Batch<\/p>\n<p>SELECT Id FROM #TempTable;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nSSMS splits scripts that contain a <span class=\"tt\">GO<\/span> 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 <span class=\"tt\">GO<\/span> and submitting it, followed by highlighting the code after the <span class=\"tt\">GO<\/span> and submitting that. The output you will get is going to look similar to this:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg\" alt=\"two batches\" title=\"two batches\" width=\"732\" height=\"526\" class=\"aligncenter size-full wp-image-1118\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg 732w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches-300x215.jpg 300w\" sizes=\"auto, (max-width: 732px) 100vw, 732px\" \/><\/a>\n<\/p>\n<p>\nThe second result set proves that the temp table was still visible in the second batch.\n<\/p>\n<p>\nIf you try to rerun the script, you will get this error message:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches_rerun.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches_rerun.jpg\" alt=\"two batches rerun\" title=\"two batches rerun\" width=\"732\" height=\"526\" class=\"aligncenter size-full wp-image-1119\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches_rerun.jpg 732w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches_rerun-300x215.jpg 300w\" sizes=\"auto, (max-width: 732px) 100vw, 732px\" \/><\/a>\n<\/p>\n<p>\nHowever, 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.\n<\/p>\n<h3>The Temp Table Name Suffix<\/h3>\n<p>\nTo 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 <span class=\"tt\">tempdb.sys.objects<\/span>. However, as you can see in the next image, the name in there is not exactly <span class=\"tt\">#TempTable<\/span>. Instead it has a very long suffix like this:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/objects_in_tempdb.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/objects_in_tempdb.jpg\" alt=\"objects in tempdb\" title=\"objects in tempdb\" width=\"880\" height=\"526\" class=\"aligncenter size-full wp-image-1115\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/objects_in_tempdb.jpg 880w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/objects_in_tempdb-300x179.jpg 300w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/a>\n<\/p>\n<p>\nThe 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.\n<\/p>\n<p>\nThat 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.\n<\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TestObjectNameLength') IS NOT NULL DROP PROCEDURE #TestObjectNameLength;<br \/>\nGO<br \/>\nCREATE PROCEDURE #TestObjectNameLength<br \/>\n  @NameLength INT,<br \/>\n  @TempTable BIT<br \/>\nAS<br \/>\nBEGIN<br \/>\n DECLARE @TblName NVARCHAR(MAX) = '';<br \/>\n DECLARE @i INT = 1;<br \/>\n WHILE(@i&lt;14)<br \/>\n BEGIN<br \/>\n  SET @TblName += 'aaaaBcccc'+RIGHT(STR(@i),1);<br \/>\n  SET @i += 1;<br \/>\n END  <\/p>\n<p> SET @TblName = LEFT(<br \/>\n   CASE WHEN @TempTable = 1 THEN '#' ELSE '' END + @TblName,<br \/>\n   @NameLength<br \/>\n );<\/p>\n<p> DECLARE @Cmd NVARCHAR(MAX) = 'CREATE TABLE '+(@TblName)+'(i INT);';<br \/>\n --DECLARE @Cmd NVARCHAR(MAX) = 'CREATE PROCEDURE '+(@TblName)+' AS RETURN 0;';<\/p>\n<p> DECLARE @Msg NVARCHAR(MAX) = 'Success';<br \/>\n BEGIN TRAN;<br \/>\n  BEGIN TRY<br \/>\n   EXEC(@Cmd);<br \/>\n  END TRY<br \/>\n  BEGIN CATCH<br \/>\n   SET @Msg = ERROR_MESSAGE();<br \/>\n  END CATCH<br \/>\n ROLLBACK;<\/p>\n<p> SELECT @TempTable TempTable,(@TblName) TblName,LEN(@TblName) Length,@Msg Outcome;<br \/>\nEND;<br \/>\nGO<\/p>\n<p>EXEC #TestObjectNameLength 129,0;<br \/>\nEXEC #TestObjectNameLength 128,0;<br \/>\nEXEC #TestObjectNameLength 117,1;<br \/>\nEXEC #TestObjectNameLength 116,1;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nThe 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.\n<\/p>\n<p>\nThe returned error messages clearly confirm the maximum name length of 128 for standard objects and 116 for temporary objects.\n<\/p>\n<h3>Dropping a Temporary Object<\/h3>\n<p>\nWe have seen that we cannot use the <span class=\"tt\">sys.objects<\/span> 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.\n<\/p>\n<p>\nHowever, we can use the <span class=\"tt\">OBJECT_ID()<\/span> function. The following query returns a non-NULL value, if a <span class=\"tt\">#TempTable<\/span> exists in the current scope:\n<\/p>\n<div>\n[sql]\nSELECT OBJECT_ID('tempdb..#TempTable');<br \/>\n[\/sql]\n<\/div>\n<p>\nImportant 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:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/database_name_is_ignored_when_referencing_temp_table.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/database_name_is_ignored_when_referencing_temp_table.jpg\" alt=\"database name is ignored when referencing temp table\" title=\"database name is ignored when referencing temp table\" width=\"880\" height=\"526\" class=\"aligncenter size-full wp-image-1112\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/database_name_is_ignored_when_referencing_temp_table.jpg 880w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/database_name_is_ignored_when_referencing_temp_table-300x179.jpg 300w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/a>\n<\/p>\n<p>\nSo, to conditionally drop a temp table you would have to execute the following code:\n<\/p>\n<div>\n[sql]\nIF(OBJECT_ID('tempdb..#TempTable') IS NOT NULL) DROP TABLE #TempTable;<br \/>\n[\/sql]\n<\/div>\n<h3>Hidden Treasures<\/h3>\n<p>\nIf you are now thinking that you could use the full names of a temp table after retrieving it from <span class=\"tt\">tempdb.sys.objects<\/span> to peek into the contents of temp tables that live in other scopes, I have to disappoint you:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/no_direct_access_to_objects_in_tempdb.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/no_direct_access_to_objects_in_tempdb.jpg\" alt=\"no direct access to objects in tempdb\" title=\"no direct access to objects in tempdb\" width=\"880\" height=\"526\" class=\"aligncenter size-full wp-image-1114\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/no_direct_access_to_objects_in_tempdb.jpg 880w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/no_direct_access_to_objects_in_tempdb-300x179.jpg 300w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/a>\n<\/p>\n<p>\nAs 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 &mdash; after all it is longer than the allowed 116 characters &mdash; the statement fails.\n<\/p>\n<h3>Temporary Procedures<\/h3>\n<p>\nSimilar to temp tables, SQL Server allows the creation of temp procedures:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_procedure.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_procedure.jpg\" alt=\"temporary procedure\" title=\"temporary procedure\" width=\"880\" height=\"526\" class=\"aligncenter size-full wp-image-1117\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_procedure.jpg 880w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_procedure-300x179.jpg 300w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/a>\n<\/p>\n<p>\nFor 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.\n<\/p>\n<p>\nSQL 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.\n<\/p>\n<h3>Procedure Scope<\/h3>\n<p>\nIf 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:\n<\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;<br \/>\nGO<br \/>\nCREATE PROCEDURE #TempProc<br \/>\nAS<br \/>\nBEGIN<br \/>\n  CREATE TABLE #TempTable(i INT);<br \/>\n  SELECT CASE WHEN OBJECT_ID('tempdb..#TempTable') IS NOT NULL THEN '#TempTable exists' ELSE '#TempTable gone' END AS Status;<br \/>\nEND;<br \/>\nGO<\/p>\n<p>EXEC #TempProc;<br \/>\nSELECT CASE WHEN OBJECT_ID('tempdb..#TempTable') IS NOT NULL THEN '#TempTable exists' ELSE '#TempTable gone' END AS Status;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nThat means that you cannot call a procedure to create a temp table for you to use later on.\n<\/p>\n<h3>To Drop or Not To Drop<\/h3>\n<p>\nWhen a temporary object goes out of scope at the end of a procedure execution, it actually does not get dropped &mdash; at least not completely.\n<\/p>\n<p>\nThe 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.\n<\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;<br \/>\nIF OBJECT_ID('tempdb..#object_id') IS NOT NULL DROP TABLE #object_id;<br \/>\nCREATE TABLE #object_id(object_id INT);<br \/>\nGO<br \/>\nCREATE PROCEDURE #TempProc<br \/>\nAS<br \/>\nBEGIN<br \/>\n  CREATE TABLE #TempTable(i INT IDENTITY(1,1), d CHAR(8000) DEFAULT '*');<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #TempTable DEFAULT VALUES;<br \/>\n  INSERT INTO #object_id(object_id) SELECT OBJECT_ID('tempdb..#TempTable');<\/p>\n<p>  SELECT t.object_id,t.name,t.type_desc,t.create_date,s.used_page_count,s.reserved_page_count<br \/>\n    FROM tempdb.sys.tables t<br \/>\n    JOIN #object_id o<br \/>\n    ON t.object_id = o.object_id<br \/>\n    JOIN tempdb.sys.dm_db_partition_stats s<br \/>\n    ON o.object_id = s.object_id;<br \/>\nEND;<br \/>\nGO<\/p>\n<p>EXEC #TempProc;<\/p>\n<p>  SELECT t.object_id,t.name,t.type_desc,t.create_date,s.used_page_count,s.reserved_page_count<br \/>\n    FROM tempdb.sys.tables t<br \/>\n    JOIN #object_id o<br \/>\n    ON t.object_id = o.object_id<br \/>\n    JOIN tempdb.sys.dm_db_partition_stats s<br \/>\n    ON o.object_id = s.object_id;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nThe 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.\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/deferred_temp_table_drop.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/deferred_temp_table_drop.jpg\" alt=\"deferred temp table drop\" title=\"deferred temp table drop\" width=\"878\" height=\"551\" class=\"aligncenter size-full wp-image-1113\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/deferred_temp_table_drop.jpg 878w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/deferred_temp_table_drop-300x188.jpg 300w\" sizes=\"auto, (max-width: 878px) 100vw, 878px\" \/><\/a>\n<\/p>\n<p>\nAfter 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.\n<\/p>\n<p>\nCreating 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.\n<\/p>\n<p>\nFor 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 &ldquo;<a href=\"http:\/\/sqlblog.com\/blogs\/paul_white\/archive\/2012\/08\/17\/temporary-object-caching-explained.aspx\">Temporary Table Caching Explained<\/a>&rdquo; by Paul White.\n<\/p>\n<p>\nThe 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.\n<\/p>\n<h3>The Outside Scope<\/h3>\n<p>\nWe 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:\n<\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable;<br \/>\nIF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;<br \/>\nGO<br \/>\nCREATE PROCEDURE #TempProc<br \/>\nAS<br \/>\nBEGIN<br \/>\n  INSERT INTO #TempTable VALUES(13);<br \/>\n  DELETE FROM #TempTable WHERE i = 42;<br \/>\nEND;<br \/>\nGO<\/p>\n<p>CREATE TABLE #TempTable(i INT);<br \/>\nINSERT INTO #TempTable VALUES(42);<\/p>\n<p>EXEC #TempProc;<\/p>\n<p>SELECT * FROM #TempTable;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nYou could even alter the schema of the temp table inside the procedure and for example add an additional column to it:\n<\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable;<br \/>\nIF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;<br \/>\nGO<br \/>\nCREATE PROCEDURE #TempProc<br \/>\nAS<br \/>\nBEGIN<br \/>\n  ALTER TABLE #TempTable ADD d INT NOT NULL DEFAULT CHECKSUM(NEWID());<br \/>\nEND;<br \/>\nGO<\/p>\n<p>CREATE TABLE #TempTable(i INT);<br \/>\nINSERT INTO #TempTable VALUES(42);<\/p>\n<p>EXEC #TempProc;<\/p>\n<p>SELECT * FROM #TempTable;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nSo 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.\n<\/p>\n<h3>Lost Temp Tables<\/h3>\n<p>\nBesides 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.\n<\/p>\n<h3>The Big Temp Table Eclipse<\/h3>\n<p>\nIf 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.\n<\/p>\n<p>\nIf 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: <\/p>\n<div>\n[sql]\nIF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable;<br \/>\nIF OBJECT_ID('tempdb..#TempProc') IS NOT NULL DROP PROCEDURE #TempProc;<br \/>\nGO<br \/>\nCREATE PROCEDURE #TempProc<br \/>\nAS<br \/>\nBEGIN<br \/>\n  SELECT 'before inner create' AS [time],* FROM #TempTable;<\/p>\n<p>  CREATE TABLE #TempTable(InnerTbl INT);<br \/>\n  INSERT INTO #TempTable VALUES(76);<\/p>\n<p>  SELECT 'after inner create' AS [time],* FROM #TempTable;<\/p>\n<p>  DROP TABLE #TempTable;<\/p>\n<p>  SELECT 'after inner drop' AS [time],* FROM #TempTable;<br \/>\nEND;<br \/>\nGO<\/p>\n<p>CREATE TABLE #TempTable(OuterTbl INT);<br \/>\nINSERT INTO #TempTable VALUES(42);<\/p>\n<p>EXEC #TempProc;<br \/>\n[\/sql]\n<\/p><\/div>\n<p>\nThis script creates a temp table <span class=\"tt\">#TempTable<\/span> with one column <span class=\"tt\">OuterTbl<\/span>. 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: <span class=\"tt\">InnerTbl<\/span>. After selecting from that table it drops it and afterwards selects from <span class=\"tt\">#TempTable<\/span> again. The output looks like this:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_table_eclipse.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_table_eclipse.jpg\" alt=\"temp table eclipse\" title=\"temp table eclipse\" width=\"878\" height=\"551\" class=\"aligncenter size-full wp-image-1133\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_table_eclipse.jpg 878w, https:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/temp_table_eclipse-300x188.jpg 300w\" sizes=\"auto, (max-width: 878px) 100vw, 878px\" \/><\/a>\n<\/p>\n<p>\nSo, inside of a stored procedure it is possible to temporarily eclipse a preexisting temporary object.\n<\/p>\n<p>\nExam question: Can you think of a situation where this line of T-SQL code will execute without error:\n<\/p>\n<div>\n[sql]\nEXEC('DROP TABLE #TempTable;DROP TABLE #TempTable;');<br \/>\n[\/sql]\n<\/div>\n<h3>Other Temp Table Scopes<\/h3>\n<p>\nA 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 <span class=\"tt\">EXEC('...');<\/span> construct is only visible inside thereof. That means &ndash; as with procedures &ndash; 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.\n<\/p>\n<h3>Summary<\/h3>\n<p>\nThis 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.\n<\/p>\n<p>\nOne 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.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\nHow long does a temp table live? Who can see and access it? What other types of temporary objects exist? What is a temp table Eclipse? This article will take an in-depth look at temporary objects and answer questions like the ones above.\n<\/p>\n<p> <a href=\"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,21],"tags":[],"class_list":["post-1109","post","type-post","status-publish","format-standard","hentry","category-general","category-tempdb"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net<\/title>\n<meta name=\"description\" content=\"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-10-16T14:00:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:54:22+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Temp Tables \u2013 Scoping &amp; Eclipsing\",\"datePublished\":\"2012-10-16T14:00:51+00:00\",\"dateModified\":\"2014-11-13T18:54:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/\"},\"wordCount\":2654,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/two_batches.jpg\",\"articleSection\":[\"General\",\"TempDb\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/\",\"name\":\"Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/two_batches.jpg\",\"datePublished\":\"2012-10-16T14:00:51+00:00\",\"dateModified\":\"2014-11-13T18:54:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/two_batches.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/two_batches.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1109\\\/temp-tables-scoping-eclipsing\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Temp Tables \u2013 Scoping &amp; Eclipsing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net","description":"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/","og_locale":"en_US","og_type":"article","og_title":"Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net","og_description":"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.","og_url":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-10-16T14:00:51+00:00","article_modified_time":"2014-11-13T18:54:22+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Temp Tables \u2013 Scoping &amp; Eclipsing","datePublished":"2012-10-16T14:00:51+00:00","dateModified":"2014-11-13T18:54:22+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/"},"wordCount":2654,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg","articleSection":["General","TempDb"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/","url":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/","name":"Temp Tables \u2013 Scoping &amp; Eclipsing - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg","datePublished":"2012-10-16T14:00:51+00:00","dateModified":"2014-11-13T18:54:22+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"How long does a temp table live? Who can see and access it? What other types of temporary objects exist? This article will take an in-depth look at temporary objects and answer questions like the ones above.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/10\/two_batches.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1109\/temp-tables-scoping-eclipsing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Temp Tables \u2013 Scoping &amp; Eclipsing"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-hT","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1109"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1109\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}