SQL SERVER 2014– Maximum Allowable Length of Objects’ Name


Edit:
As Tom Cooper pointed out from MSDN forum, the maximum length of both table and column is still 128.

  

DECLARE @name sysname
SELECT @name = REPLICATE(N't', 256)
SELECT LEN(@name)  ---128

 

The table name identifier  in SQL Server is stored in database as sysname. In SQL Server 2014, the length is 256 but in SQL Server 2008 R2  it was 128.

You can name a table name with the length 256 but the temp table it is still limited to 116.

  
 
--table 256
DECLARE @name sysname,@sql nvarchar(300)
SELECT @name = REPLICATE(N't', 256)
SELECT @sql = 'CREATE TABLE '+@name+' (id int)'
PRINT @sql
EXEC(@sql)
--temp table 116
SELECT @name = REPLICATE(N't', 115)
SELECT @sql = 'CREATE TABLE #'+@name+' (id int)'
PRINT @sql
EXEC(@sql)

/* In SQL Server 2014 (may be SQL Server 2012) the maximum length of table and col as 
object name from sysname data type is 256
*/
DECLARE @name1 sysname,@sql1 nvarchar(4000), @col1 sysname
SELECT @name1 = REPLICATE(N't', 256),@col1 = REPLICATE(N'c', 256)
SELECT @sql1 = 'CREATE TABLE ' +@name+ '('+@col1+ ' int)'
PRINT @sql1
EXEC(@sql1)
---–I tested this scrip from SQL Server 2008 R2 and SQL Server 2014 and they are working.
 
 

 
Advertisements


Leave a Reply

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