TempDB Catalog view not updated after Local Temporary Table Drop inside Procedure - by Shah Anup

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 794293 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/17/2013 1:45:26 PM
Access Restriction Public


Hello Team, 
Recently I notice a strange behavior about TEMPDB catalog view, SYSOBJECTS or INFORMATION_SCHEMA.TABLES. For illustration please use the attached code. 
1 table script and 2 stored procedures.

Pr_Test2 is calling Pr_Test1 3 times using while loop
Pr_Test1 creates local temp table from regular database table, and then drops the local temp table

Unexpected Behavior:
Even after Explicitly performing DROP on local temp table, if we query any of the TEMPDB catalog view, TEMPDB.SYS.SYSOBJECTS or TEMPDB. INFORMATION_SCHEMA.TABLES they still have an entry for the same table.
Though actual table is actually dropped and user will not be able to access that after drop statement but in situations where query logic is inquiring about “what were the columns on that local temp table it will be still accessible”.
Sign in to post a comment.
Posted by Ajay [MSFT] on 1/7/2014 at 10:50 AM

Thanks for submitting this feedback. This is expected behavior. SQL Server has introduced performance optimizations for temp tables by caching 1 IAM and 1 data page provided they meet the criteria for caching.

For more information see, http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx

Ajay Jagannathan
Senior Program Manager
Posted by Vladimir Moldovanenko on 7/18/2013 at 8:04 AM
See couple good articles for explanation.

this is a benefit, not a drawback. you just have to account in your logic for this.