As a SQL Server developer or administrator, you may have come across the concept of temporary tables. Temporary tables are a useful feature in SQL Server that allow you to store and manipulate data temporarily within a session or a specific scope. They are particularly handy when you need to perform complex calculations or store intermediate results.

During one of my recent SQL Server Performance Tuning Practical Workshops, a participant asked me an interesting question about temporary tables. They wanted to know how to determine the count of how many times a single temporary table has been regenerated. This question intrigued me because it’s not something I encounter frequently. However, I was able to provide an answer.

Before we delve into the answer, I would like to refer you to an insightful blog post written by my friend, which discusses the maximum allowable length of characters for temporary objects in SQL Server. It provides valuable information about the limitations of temporary table names.

Now, let’s address the question at hand. When a temporary table is created in SQL Server, it is internally assigned a name with 128 characters. The last 12 digits of this name contain a unique hexadecimal number, also known as magic bits. This number indicates how many times the table has been created since the last SQL Server restart. It’s important to note that this count resets when the SQL Server services are restarted.

To demonstrate this concept, you can run the following query multiple times in SQL Server Management Studio:

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

Each time you execute the above script, you will notice that the last digit in the temporary table’s name increases by 1. This indicates the number of times the table has been regenerated.

I find this aspect of SQL Server fascinating, as it provides insight into the internal workings of temporary tables. It’s a lesser-known feature that can be useful in certain scenarios.

I hope you found this information helpful. If you were already aware of this aspect of SQL Server, I would love to hear your thoughts in the comments section below.