#0224 – SQL Server – Temporary Table Naming – Maximum Allowed Length and associated naming logic


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'))

0223

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.

Advertisements

2 thoughts on “#0224 – SQL Server – Temporary Table Naming – Maximum Allowed Length and associated naming logic

  1. Pingback: #0376 – SQL Server – Msg 2714: There is already an object named ‘#tableName’ in the database. | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s