Recently, I was playing around with temporary tables and recalled a post that I had read earlier this year from Mr. Balmukund Lakhani’s (Blog | Twitter | Site) guest post on Pinal’s blog – SQLAuthority.com.

The post talks about the maximum allowable length of characters when naming of temporary tables. While normal table names have a maximum possible length of 128 characters, the temporary tables are restricted to a length of 116 characters. Whenever a temporary table is created, SQL Server pads the supplied table name with underscores (_) and a 12 digit number to make the total come out to 128 characters.

This restriction and logic around naming conventions of temporary tables is necessary because temporary tables with the same name can be created by different sessions. However, what attracted my attention was the 12 digit number after the padding – how did SQL Server generate the 12-digit number?

To get an idea of how the 12-digit number used after padding the temporary table name is generated, I ran a small test wherein I created three temporary tables and use the OBJECT_NAME() function to get the actual object name used in the given session.

USE tempdb
GO

DROP TABLE #t1
DROP TABLE #t2
DROP TABLE #t3

CREATE TABLE #t1 (tId INT)
CREATE TABLE #t2 (tId INT)
CREATE TABLE #t3 (tId INT)

SELECT OBJECT_NAME(OBJECT_ID('#t1'))
SELECT OBJECT_NAME(OBJECT_ID('#t2'))
SELECT OBJECT_NAME(OBJECT_ID('#t3'))

As can be seen, the number being used in the padding is simply an auto-incrementing number – indicating the number of the temporary table that is being created. Whenever the SQL Server service restarts, the counter is reset.

This being said, it is always better to use the OBJECT_ID() function (as shown in the query above) to get the unique object Id value associated to the temporary table within the given user session.

Until we meet next time,

Be courteous. Drive responsibly.