SQL SERVER – How many Minimum and Maximum Characters allowed all Object of SQL Server

Question: How many Maximum characters can take a User defined Table?

Question: How many Minimum characters can take a User defined Table?

Question: How many Minimum and Maximum Characters allowed all Object of SQL Server?

Question: Why Temporary Table cannot contain more than 116 characters in Temp Table?

Question:  Can we create more than one Temporary Table in different session?

All object name can have minimum 1 character and maximum 128 characters in the SQL Server. But in the scenario of Temporary Table you can take only 116 characters.

But question coming “Why Temporary Table cannot contain more than 116 characters only in Temp Table”

So now using some script we will understand this magic of SQL Server.

-- Create user defined object (Table)

Step 1: For Noraml Table Object

--1) Below Query will return the Query of created table which contain the 128 characters

DECLARE @Tabname NVARCHAR(800)

SELECT @Tabname = REPLICATE('Z', 128)

SELECT @Tabname = 'CREATE TABLE '+@Tabname+'(Col1 int)'

PRINT @Tabname

EXEC(@Tabname)

-- Result of above query, Run given result Script

CREATE TABLE ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ(Col1 int)

-- Selecting the Table exist or not

Select * from ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

-- Selecting the Lenght of Table

SELECT LEN(name) 'LengthOfTable', name as NameOfTalbe

FROM tempdb.sys.objects

WHERE TYPE = 'u'

AND name LIKE 'zzzzzz%'

-- Result

LengthOfTable NameOfTalbe

------------- ---------------------------------------------------------

128           ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

(1 row(s) affected)

Step 2: For Temporary Table Object

Now come to the Temporary Table Object. The following Script explaining the concept-

-- 2) Now creating the Temp Object

USE tempdb

GO

DECLARE @Tabname NVARCHAR(800)

SELECT @Tabname = REPLICATE('X', 128)

SELECT @Tabname = 'CREATE TABLE #'+@Tabname+'(Col1 int)'

PRINT @Tabname

EXEC(@Tabname)

Result with ERROR

CREATE TABLE #XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX(Col1 int)

Msg 193, Level 15, State 1, Line 1

The object or column name starting with '#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' is too long. The maximum length is 116 characters.

-- Now creating Temp table with 116 characters

DECLARE @Tabname NVARCHAR(800)

SELECT @Tabname = REPLICATE('X', 115)

SELECT @Tabname = 'CREATE TABLE #'+@Tabname+'(Col1 int)'

PRINT @Tabname

EXEC(@Tabname)

-- Result and Run given result

CREATE TABLE #XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX(Col1 int)

-- Result

Command(s) completed successfully.

-- Than Find the Length of the Temp Table.

SELECT LEN(name) 'LengthOfTable', name as NameOfTalbe

FROM tempdb.sys.objects

WHERE TYPE = 'u'

AND name LIKE '#xxx%'

-- Result See Full Result

LengthOfTable NameOfTalbe

------------- ------------------------------------------------------

128           #XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX000000000007

(1 row(s) affected)

This is rectified here what is the problem when we create the Temp Tablr Object. In temp table contain the last 12 Digit space of itself and it’s used by TempDatabase because SQL Server has given the authority to create Multiple same name of Temp Object in different session. So that temp table contain 128 charactres but it reserved the last12 characters.