SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations

The best part of my SQL Server Performance Tuning Practical Workshop is that at the end of the workshop we spend quite a good amount of time to solve the issues users have in their production system. During one of the recent workshop, I was asked a very interesting question  about temporary tables.

 “How do we know the count of how many times a single temporary table has been regenerated?”

I particularly found this very interesting as so far in my career, I have been asked this only 3 times. The best part is that I knew the answer.

However, before we start answering this question, I must refer you to another amazing blog post written by my friend over here SQL SERVER – Maximum Allowable Length of Characters for Temp Objects is 116. This blog post contains very interesting information about the max size of the temporary table name.

Now let us read the answer of the question – how do we know how many times any particular temporary table has been regenerated.

When we create any temporary table, it is internally created with a 128 characters in its name. The last 12 digits of this long name contain unique magic bits or a hexadecimal number which indicates how many times the table has been created so far since the last SQL Server restart.

This number resets when SQL Server services are reset.

You can recreate this example by running the following query multiple times in the SQL Server Management Studio. Every single time when you re-run this query, it will increment one in the number which is at the end of the temporary table’s name.

-- Create TempTable
CREATE TABLE #TempTable (Col INT);
-- Check TempTable Name
SELECT [name]
FROM tempdb.sys.tables
WHERE [name] LIKE N'#TempTable%';
-- Drop TempTable
DROP TABLE #TempTable;

Here is the example when I ran above script multiple times in the SSMS and you will notice the last digit increasing by 1 every time I run the script.

Please leave a comment let me know if you knew about this part of SQL Server.

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

SQL Scripts, SQL Server, SQL Server Management Studio, SSMS, Temp Table
Previous Post
SQL SERVER – How to See Scripts Executing in sp_executesql?
Next Post
SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio

Related Posts

2 Comments. Leave new

  • plz give me to a simple project code

  • Jeffery Williams
    November 24, 2017 7:56 am

    After 19 this turns I THINK into HEX? So I get 19, then 1A, 1B, and so on. So there is that as a question any way to get an actual numeric count? Also is there a way I can set this to a variable and include this in my EventLog (Not the built in SQL Logging but my own.


Leave a ReplyCancel reply

Exit mobile version