Recently a friend posted an interesting question on the SQLServerFAQ community page since the answer was new to almost all in the group, I have taken idea to write a blog post to explain this in detail. Here we go.
All objects can have minimum 1 and maximum of 128 characters in their names in SQL Server. Only exception is local, temporary tables that can have a maximum of 116 characters. What’s the technical reason behind this? Any one, why that’s so?
Manas (B | T) posted this link from books online which tells this limitation, but I always wanted to explore what happens under the hood. Here is the simple reproduction of the problem statement
DECLARE @i NVARCHAR(800) SELECT @i = REPLICATE('A', 116) SELECT @i = 'CREATE TABLE #'+@i+'(i int)' PRINT @i EXEC(@i)
In above script I am trying to create a temp table (starts with #) and you would see below output.
CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Msg 193, Level 15, State 1, Line 1
The object or column name starting with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. The maximum length is 116 characters.
The URL which was pointed by Manas clearly explained this
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
If you try to create a normal table with more than 128 characters then you would see
Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. Maximum length is 128.
If you read the message closely, there are two error numbers 193 and 103 which have different length. Now the real question is why there is differentiation between regular table and temp table Let’s create two temp tables now. I am giving a name as A and A [repeated 128 times]
DECLARE @i NVARCHAR(800) SELECT @i = REPLICATE('A', 115) SELECT @i = 'CREATE TABLE #'+@i+'(i int)' PRINT @i EXEC(@i) GO DECLARE @i NVARCHAR(800) SELECT @i = REPLICATE('B', 1) SELECT @i = 'CREATE TABLE #'+@i+'(i int)' PRINT @i EXEC(@i) GO
Here is the output, which you would see. Now execute the same output in the SSMS.
CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT) CREATE TABLE #B(i INT)
If you run above piece of code in SSMS, you would see object getting created. Now, let’s find out the name of the object in the tempdb database
SELECT LEN(name) 'Length', name FROM tempdb.sys.objects WHERE TYPE = 'u' AND name LIKE '#AAAAAA%' OR name LIKE '#B%'
Here is the output.
The length of both the object name is 128 even if we create an object with the length name as 116 and 1. That should give you a little clue about the behavior.
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, it’s 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 allows for temp objects as SQL adds 12 chars at the end.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Good observation. thanks for sharing
Thanks for sharing. nice read
Excellent Article, Thanks for sharing.
That’s interesting. Thanks for sharing!
Learn something new, not at all aware of this concept
very interesting article, thanks for sharing
Good one.. Thanks for sharing..