NVARCHAR length shows up doubled

An experienced DBA I know didn’t realize this: if you look at sp_help or sys.columns for an NVARCHAR column, the max_length value will show up as double the allowable length.

Let me explain.

CHAR and VARCHAR length is normal

Let’s say we have a “val” column in a table, and it’s of type VARCHAR(10).

CREATE TABLE test (val varchar(10));
GO
EXEC sp_help 'test';

When we run that sp_help statement, we see that the max_length for val is 10:

You’ll see the same thing with the query SELECT name, max_length FROM sys.columns WHERE object_id = object_id(‘test’);

NCHAR and NVARCHAR length displays as doubled

Now we create a new table, and “val” is of type NVARCHAR(10).

CREATE TABLE  ntest (val nvarchar(10));
GO
EXEC sp_help 'ntest';

For some ungodly reason, sp_help shows that max_length is 20:

And again, you’ll see max_length = 20 with the query SELECT name, max_length FROM sys.columns WHERE object_id = object_id(‘ntest’);

But the nvarchar length is not REALLY doubled

Let’s test this out. Both of the tables have a column that is (10) long. So these will work:

INSERT INTO test (val)  VALUES ('1234567890');
INSERT INTO ntest (val) VALUES ('1234567890');

But these will both return a “String or binary data would be truncated” error:

INSERT INTO test (val)  VALUES ('123456789011');
INSERT INTO ntest (val) VALUES ('123456789011');

Speculation & Answers

My DBA friend said, “It must be showing bytes instead of characters.”

Yes, and also no. Here:

• CHAR/VARCHAR datatype uses one byte per character
• NCHAR/NVARCHAR uses two bytes per character

BUT we also know that VARCHAR and NVARCHAR use 2 extra bytes to record how much space was actually used in that variable-length string. So no, it’s not the storage space for the field.

Microsoft provides us the answer:

“…in NCHAR(n) and NVARCHAR(n) the n defines the string length in byte-pairs (0-4,000). n never defines numbers of characters that can be stored. This is similar to the definition of CHAR(n) and VARCHAR(n).”

Docs.Microsoft.com, “nchar and nvarchar”

Oh well, there you go then!