SQL SERVER – Inside Temp table Object Creation – Part II

SQL SERVER - Inside Temp table Object Creation - Part II helpring After my previous article on “Inside Temp table object creation”, I had a number of people ask me more about this topic. There seems to be a need for understanding these basic fundamentals that we have known for ages in a refreshingly new way. Though there were tons of mails that had come to me, there was one mail that caught my attention and I was pleasantly surprised to see the same. I thought it was worth the time to explain the same here over the blogs again. The Email reads like:

“Hi Pinal,

I really liked your article on Inside TempDB Table creation article. And the steps were very useful for me to understand how tempdb works. As a fresher and into my first year of career, I somehow found courage to send you this email. I am not sure if you will reply, still thought you will have some pointers to help me. When I did the step on my local laptop, I could see the exact steps shown by you. I was so excited and I thought let me know what our dev / test environments look like and I ran the DMV for user tables there.

I do get to see a number of objects with names like #B0D42C6C, #D5F46A7C and so on. They are not like the table names that you mention inside your previous article. And even after I run the same DMV after 5-10 mins, I see a lot of them still on my system. What are these tables? They also have negative ID value as you mentioned before. Are they generated by system? Are they used by my application? How do I get to know about them?”

I think my friend you have brought a different dimension to this article. I am glad you found time to write to me and I appreciate each and every one who takes time to write to me. There is no simple questions in this world. I think this is yet another example of complex SQL can be and how we can learn from SQL Server every single day.

Now let me explain you what these temp objects are which my friend is talking about. These are also temp tables created by user but inside a Stored Procedure. To illustrate the same I am going to show you inside a code block below:

Let us first create the stored procedure that creates a temp table inside the SP:

USE AdventureWorks2012
DROP PROCEDURE TempTable_Objects
#temptable_in_sp (
name CHAR(30) NOT NULL
-- Do what ever you want with the temp table :)
-- WAIT, so that the SP doesn’t complete fast :)
WAITFOR DELAY '00:00:05'

Once the stored procedure is created, let us open two sessions and execute the stored procedure in one window and then execute the objects created inside TempDB on the second window. The two session windows are:

Session 1: Run the SP

-- Execute the SP in Session 1
EXEC TempTable_Objects
WAITFOR DELAY '00:00:05'
EXEC TempTable_Objects

Session 2: Track the Object creation inside TempDB

-- Results when SP started
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the first SP call ended
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the SP was called again
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The output for the same look like:

SQL SERVER - Inside Temp table Object Creation - Part II temp_table_object_creation_II-01

As you can see, the object name is similar to the previous article. But unlike the standard temp table creation, here SQL Server is caching and keeping this so that the subsequent users can reuse the same. Hence in this particular case, since my friends said he saw a number of such values in TempDB, it means there are lots of temp tables created via the stored procedure which are still there post the session ending. The temp table object ID confirms our assumption that it is getting reused. On further quizzing my friend, I found there were reporting queries which are generating temp tables every 15-20 mins for in a scheduled manner. These were a constant behavior in their environment.

If you check SQL Server Management Studio Object Explorer, we can see these objects there too. Here is a typical snapshot of the same.

SQL SERVER - Inside Temp table Object Creation - Part II temp_table_object_creation_II-02

As I said before, these are great learning experience for me too and hope you learnt something new today with me in this exploration.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Temp Table
Previous Post
SQL SERVER – Customization of New Query in SQL Server Management Studio
Next Post
SQL SERVER – How to Learn SQL Server 2014 – Video Tutorial

Related Posts

Leave a Reply