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.

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.

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

• sp_help [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.