Press "Enter" to skip to content

Preconceived Notions around Temp Tables

Two overlapping posts on temp tables? Order up! First, Deb Melkin realizes tempdb is like a hotel room:

I was always taught to clean up after myself so I thought dropping my local temp tables after creating them in a proc did that. I knew that once the session was dropped, the table no longer was there so SQL Server would eventually take care of it. But then you had the cases where you ran the same stored proc multiple times in the same session so you wanted to make sure the table was gone at the end of your procedure to prevent issues.

Second, Deepthi Goguri learns the importance of getting temp tables right the first time:

I always had an opinion of changing the schema of the temp tables or adding indexes later after the temp table creation doesn’t affect in any way but it’s actually not a good idea. Yes, you heard that right. Altering or adding indexes later after temp table creation within the stored procedures will be marked for deletion and so the cached temp table metadata cannot be reused. It is suggestable to create the index as you create the temp table using the inline index syntax.

Both reference Pam Lahoud’s tempdb talk at EightKB (video), so check that out too.