Some index maintenance scripts or dynamic SQL scripts use SQL object names as values for variables or columns i.e. database name, table name, index name etc.
SQL Server object names can be at most 128 characters long, so common wisdom is to declare the holding variable or column as SYSNAME data type or one of the alphanumeric datatype with a width of 128 characters.
Example –
declare @dbname sysname declare @tablename nvarchar(128) declare @indexname varchar(128)
This is technically correct.
Another technically correct thing to do is to use the QUOTENAME function to wrap the object name in [ and ] brackets. This handles those cases where there are special characters in the name or the name is a reserved keyword.
Example –
set @dbname = quotename(db_name(db_id())) set @tablename = quotename(object_name(object_id)) set @indexname = quotename(object_name(object_id))
The Issue
The brackets will add two more characters to the value.
So for really long object names that are 128 characters in length, the value would be 128 + 2 = 130 characters.
The two extra characters will break the variable assignment or row insert statements with truncation error –
| String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). |
In my shop, we do have some long index names and I have had to debug some scripts for this issue. And this is not the first time!
The Solution
I suggest that you use 130 as the width for the variables or columns that will store an object name. SQL Server object names can be maximum 128 characters long so using 130 characters in scripts will handle the extra two characters.