sp_help returns the wrong length, or does it?

This is just a quick blog about a basic sp_help / data type storage gotcha.

I was working with a contractor today who was having difficulty providing me back details on a table definition.  I was specifically interested in a particular column's data type and size.  (This was related to an ETL process I was working on, and my desire to avoid any implicit conversions).

The reply I got back was, "the column you're interested in is an nvarchar(100)".  After continued digging and troubleshooting, I was eventually able to sort out that it was actually an nvarchar(50).

I put together this TEST table to illustrate where the confusion came from.  Can you spot what's going on?

INFORMATION_SCHEMA.COLUMNS returns nvarchar (50), but sp_help returns nvarchar(100)!  Surely there's a bug with SQL Server!!!

uh, no.

sp_help returns the, "physical length of the data type (in bytes)."

 In simplistic terms:
char - The storage size is n bytes.
nchar - The storage size is two times n bytes.

Details and References are here:
char and varchar
nchar and nvarchar
Data Types

No comments:

Post a Comment