SQL SERVER – Inside Temp Table Object Creation

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'


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:

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.

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)