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;
GO

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)