Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Dec 20 2012 12:00AM by Nakul Vachhrajani   

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.

Tags: #SQLServer, SQL Server, Development, DBA, Administration, T-SQL, #TSQL


Nakul Vachhrajani
4 · 36% · 11645
6
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

1  Comments  

  • Readers may find this interesting too http://beyondrelational.com/modules/2/blogs/70/posts/10940/internal-storage-of-temporary-table-names.aspx Also make sure to read comments as well

    commented on Dec 21 2012 12:46AM
    Madhivanan
    3 · 40% · 12947

Your Comment


Sign Up or Login to post a comment.

"#0224 - SQL Server - Temporary Table Naming - Maximum Allowed Length and associated naming logic" rated 5 out of 5 by 6 readers
#0224 - SQL Server - Temporary Table Naming - Maximum Allowed Length and associated naming logic , 5.0 out of 5 based on 6 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]