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!!!
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