SQL SERVER – Inside Temp Table Object Creation

SQL SERVER - Inside Temp Table Object Creation helphand When I wrote the article around tempdb like normal database, I had a number of people asked me how it is so simple to understand? Well, they were asking how are the tables created? How will same tables created by different sessions created inside tempdb? Are there anything different about temp tables when compared to normal tables?

These are all valid questions and sometimes we need to check the output to understand them better. So here is a simple test we are going to do to understand how temp tables are created inside SQL Server. For my example I will create a temp table of the following definition.

-- Session 1: Table creation
CREATE TABLE #temptable_test (
id INT NOT NULL IDENTITY (1,1)
,
Name CHAR(100) NOT NULL
,
DOJ DATETIME NOT NULL
);

After the table is created, we will go ahead and check the object definition created inside tempdb database. We can look at the metadata using:

-- SHOW USER TABLE
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

SQL SERVER - Inside Temp Table Object Creation object_creation_table-01

The important point to note is the negative object id for the table created. Also we need to see that the table created has a long number of underscores and a number (___02). Now why is SQL Server doing this? Why couldn’t SQL Server create the table using the same name we created? The answer is simple. We have a single tempdb for the whole instance and if there are multiple sessions creating the same object, then we don’t want to have conflict in the table created.

To mimic this, let us go ahead and create a second table with the same name but different schema to check what SQL Server does. Our second table definition looks like:

-- Session 2: Table creation
CREATE TABLE #temptable_test (
id INT NOT NULL IDENTITY (1,1)
,
FullName CHAR(100) NOT NULL
);

Though the names are same but the column definitions are different, what we see inside SQL Server tempdb metadata is:

SQL SERVER - Inside Temp Table Object Creation object_creation_table-02

As you can see, the names have different auto number extension making each of these objects unique. This is the reason why SQL Server adds the extra extension. For simplicity sake and as explained in the precious article, the temp table is removed as soon as the session is closed / completed. So let me go ahead to check the same. We will close the session 1 and see the effect.

SQL SERVER - Inside Temp Table Object Creation object_creation_table-03

As you can see, temp tables are similar to normal tables with the exception that the table is disposed as soon as the session is closed.

Do let me know if you have seen this behavior in your environments and how many such objects are available at any point in time in your environments today? Let me know and it will be a great learning about your very own environment.

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

Previous Post
SQL SERVER – Maintenance Plan – Maintenance Cleanup Task not Deleting Files
Next Post
SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056

Related Posts

No results found.

7 Comments. Leave new

  • Hi Pinal,

    Hope you are in good health.
    I tried the same thing on my sql server instance. Created 2 temp tables with same name in different sessions. But i didn’t found the negative object id for these 2 tables.

    Please suggest whether this is right or i did something wrong.

    Regards,
    Mithun

    Reply
  • Hi Pinal,

    I have checked temp tables in my Server but as stated by you there is negative ID for this but in my server IDs are not negative for temp tables and also some names are not suffixed by any number.

    See below result set:
    #7E388A4D 2117634637 U 1 0
    #7EA29EA1 2124586657 U 1 0
    #t1_________________________________________________________________________________________________________________0000000002A3 2133634694 U 1 0
    #7F96C2DA 2140586714 U 1 0

    Can you clarify on this?

    Thanks,
    Subhash

    Reply
  • Eng.Motafa Elmasry
    November 27, 2014 2:22 pm

    I do it on my Environment but it not appear any thing with me

    Reply
  • Vladimir-Cristian
    November 28, 2014 5:53 pm

    Hi Pinal! I have a complex situation with temp tables.
    I have a stored procedure uspFireMeNow which creates #fireList (field1, field2) and executes uspFireMeNow2. In this second SP I have a syntax which creates the following table #fireList(field1, field2,…, nfield1, nfield2…) without any error, so I will have two temporary tables with the same same on the same session with different object ids. Now if I run “Select * from #fireList” in child SP, SQL Server returns field1, field2,…, nfield1, nfield2…. If I run “Select nfield2 from #fireList”, SQL Server throws me an error with “Invalid column name ‘nfield2′” message. So “Select *” statement points to the second temp table since “Select nfield2” points to the first one, so I needed to change the name of one of the temp tables to fix this, and I will be carefully with naming temp tables. Do you have an explanation of this situation?

    Thanks,
    Vladimir

    Reply
  • Note that the concept of negative object ids for user-created temporary tables is new to SQL Server 2012. I think the whole concept of negative object ids is new to SQL Server 2012. In earlier versions the only distinction is the underscores after the name!

    Reply

Leave a Reply