Are You Following SQL Erudition yet?!

Photo credit: https://www.flickr.com/photos/kalexanderson/5696097036/

Like what you read here at SQL Erudition?! Why not follow the blog to get an email notification for new posts?

WordPress users can simply click on the Follow button. The button is at the top of this page or in the sidebar.

Not a WordPress user?! No issues! Follow via email address in the Follow|Subscribe field in the sidebar of this page and you are done!

Or maybe follow on Twitter!

Free Developer Editions of SQL Server 2014 and 2016

Microsoft is releasing its Developer Editions of SQL Server 2014 and 2016 for free!

Developer Edition is the same as the Enterprise Edition but it is licensed for development and testing purposes only.

The Developer license for the previous versions (2008 R2, 2012) used to go for about $50 and up. Another option was to download the fully functional edition valid for 180-days and later buy a licence.

More information about Microsoft’s new approach to developer licensing is at our Facebook post –

Use 130 Characters to Store an Object Name

SQLErudition.wordpress.com-Use-130-Characters-to-Store-an-Object-Name

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.