What is N in nvarchar? – Interview Question of the Week #301

Question: What does the N in nchar and nvarchar stand for? National. Their SQL synonyms include national character and national character varying. In SQL Server these types store Unicode text.

There is a second little question hiding inside the first. The N in the type’s name and the n inside its length declaration are not the same idea.

What Does n Mean in nvarchar(n)?

It’s the maximum length in two-byte units, not necessarily the number of characters. A basic Unicode character generally occupies one such unit; a supplementary character encoded as a UTF-16 surrogate pair uses two. The valid forms are nvarchar(n) and nvarchar(max).

Here is one supplementary character built from its two UTF-16 units, without relying on the script file’s character encoding:

DECLARE @s nvarchar(2) = NCHAR(0xD83D) + NCHAR(0xDE00);
SELECT @s AS ExampleCharacter,
       LEN(@s COLLATE Latin1_General_100_CI_AS_SC) AS Characters,
       DATALENGTH(@s) AS StoredBytes;

Under the supplementary-character collation, LEN reports 1 character while DATALENGTH reports 4 bytes. It still needs both units of nvarchar(2). LEN also excludes trailing spaces, so it’s not a storage-byte measurement.

The N Before a Literal Is Another Clue

N'Hello' is a Unicode string literal. With text outside the source code page, omitting that prefix can lose characters before a later conversion to nvarchar. SQL Server 2019 also introduced UTF-8 collations for varchar, so the blanket claim that varchar can never store Unicode is wrong.

So there are two answers: national in the type name, and two-byte units in nvarchar(n).

The n in nvarchar(n) is not a character count, it is a count of two-byte units, and one emoji uses two of them.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.