Why Temp table Name has maximum of 116 character length, whereas other has 128??? and SP_Server_Info

 

EXEC SP_SERVER_INFO is a procedure which gives server related information from your SQL Server; it returns a list of attribute names and matching values for attributes.

attribute_id attribute_name attribute_value

1 DBMS_NAME Microsoft SQL Server

2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0

10 OWNER_TERM owner

11 TABLE_TERM table

12 MAX_OWNER_NAME_LENGTH 128

13 TABLE_LENGTH 128

14 MAX_QUAL_LENGTH 128

15 COLUMN_LENGTH 128

16 IDENTIFIER_CASE MIXED

17 TX_ISOLATION 2

18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52

19 SAVEPOINT_SUPPORT Y

20 MULTI_RESULT_SETS Y

22 ACCESSIBLE_TABLES Y

100 USERID_LENGTH 128

Here we can see the TABLE_NAME attribute is 128, which means you can have table name 128 character long, though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character

Why temporary table name has maximum character length of 116, whereas others have 128, let’s find out the technical reason behind it.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)

CREATE TABLE #B(i INT)

SELECT LEN(name) 'Length', name

FROM tempdb.sys.objects

WHERE TYPE = 'u'

AND name LIKE '#AAAAAA%'

OR name LIKE '#B%'

Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

Be in this world with your code; make sure your code speak loud!

 

Post Reference: Vikram Aristocratic Elfin Share