Unicode in SQL Server: Characters, Bytes, and Lost Text

Changing a column to nvarchar does not repair text that was already converted to question marks before insertion. Unicode correctness is an end-to-end property: source decoding, application strings, driver parameters, SQL expressions, column types, and export encoding must all preserve the same characters.

Separate character count from storage size

In nvarchar(n), n represents UTF-16 byte-pairs, not a guarantee of n user-perceived characters. Supplementary characters can require two such units. A displayed character may also consist of multiple code points, such as a letter followed by a combining accent. Application limits based on visible characters and database limits based on storage units need not agree.

DECLARE @text nvarchar(20)=N'café ';
SELECT LEN(@text) AS LengthWithoutTrailingSpaces,
       DATALENGTH(@text) AS StorageBytes;
DECLARE @emoji nvarchar(2)=N'😀';
SELECT LEN(@emoji COLLATE Latin1_General_100_CI_AS_SC) AS Characters,
       DATALENGTH(@emoji) AS StorageBytes;

The first measurement demonstrates that LEN ignores trailing spaces while DATALENGTH measures storage bytes. Do not use LEN alone to validate whether exact text, including terminal spaces, was preserved. The second measurement uses a supplementary-character-aware collation to count the emoji as one character while its nvarchar representation occupies four bytes.

The N prefix creates a Unicode literal. Without it, conversion through the relevant non-Unicode code page may lose characters before assignment to nvarchar. Parameterized application code should likewise specify the intended Unicode parameter type instead of relying on accidental inference. Include actual non-Latin text in tests, not just ASCII names.

Compare nvarchar and UTF-8 deliberately

SQL Server 2019 introduced UTF-8 support for varchar under suitable UTF-8 collations. That does not make every existing varchar column Unicode. The selected collation and the entire expression path matter. For varchar(n), n is a byte limit, so a field that fits n ASCII characters can hold fewer multibyte characters.

UTF-8 may reduce storage for predominantly ASCII content, while other writing systems require more bytes per character. nvarchar and UTF-8 therefore should be compared using representative values and index sizes, not a blanket assumption that one always saves half the space. SQL Server version, column limits, and client compatibility are part of the choice.

Changing encoding or collation can also affect uniqueness and comparisons. Case-insensitive or accent-insensitive comparisons may consider visually different inputs equal. Conversely, visually identical text can have different underlying code-point sequences. Define normalization and matching rules in the application contract; do not assume choosing Unicode automatically defines identity semantics.

Test the complete text journey

Prepare a corpus containing Latin accents, Cyrillic, CJK, emoji, combining marks, quotes, and meaningful trailing spaces. Send it through the actual API, parameterized insert, select, serialization, and export path. Compare the returned sequence to the original and inspect bytes where an exact match is required. A successful insert alone tests only one stage.

During a migration, profile maximum byte lengths and values near declared limits before altering columns. Check dependent indexes, constraints, computed columns, and integrations that still bind varchar parameters. A staging copy and a round-trip comparison can reveal truncation or replacement that an aggregate row count misses.

If historical data already contains replacement characters, determine whether the original source still exists. A type conversion cannot infer the lost text. Recover from the original feed or a verified backup and document which rows cannot be restored accurately.

The operational goal is specific: the user enters a supported character sequence and receives that same sequence back, subject only to explicitly agreed normalization. Storage selection supports that guarantee, but driver settings and transformation boundaries are just as important as the column definition.

Technical references: Microsoft Learn: nvarchar · Microsoft Learn: Unicode and UTF-8 · Microsoft Learn: DATALENGTH.