#0313 – SQL Server – sp_help returns double the field length for Unicode characters


Recently, I was asked a good question by an intern. As he was going through our database design, he noticed that the column lengths of character columns were coming out to be exactly the double of what was mentioned in our documentation. When he approached me, I asked him just one question – “What are you using to validate the column length?” As expected the answer was – “The System stored procedure – sp_help”. The question therefore is:

Why would sp_help report a column length that is double than what is expected for a string column value?

The answer lies in knowing whether a particular character column supports Unicode or not. When working with Unicode data, if the collation code page does not use double-byte characters, the underlying storage requirement is two times the string length defined when defining the column.

An Example

The AddressLine1 and AddressLine2 fields in the Person.Address table of the AdventureWorks2012 sample database are defined to be 60 characters in length (see screenshot below), but when checked using sp_help, they show the length as being 120.

image

USE AdventureWorks2012 ;
GO
SELECT  DATABASEPROPERTYEX(DB_NAME(), 'Collation') 
        AS DBCollationName ;
GO

sp_help [Person.Address] ;
GO

The results of this query are shown below, which clearly show the doubled length value.

image

The reason for this discrepancy is that sp_help does not report the number of characters, but reports on the maximum number of bytes that the column can occupy.

In this case, both the AddressLine1 and AddressLine2 columns are 60 characters in length, but because the column is a NVARCHAR column, it occupies 120 bytes which is reported by the sp_help system stored procedure.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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