How SQL Server Stores Strings: varchar, nvarchar, and What UTF-8 Changed

The word “café” is four characters. Ask SQL Server how many bytes it takes to store, and the honest answer is “it depends,” which is a deeply unsatisfying thing to hear about four letters. Depending on the data type and collation you pick, that one word occupies 4 bytes, 5 bytes, or 8 bytes, and a three-character Japanese word can land anywhere from 6 to 9. This post pulls a real page apart, byte by byte, to show exactly where those differences come from and what the UTF-8 collations added in SQL Server 2019 actually do on disk.

This is a companion to the earlier byte-level walkthroughs of how SQL Server stores a datetime and where it stores each column. If you have not seen a record taken apart with DBCC PAGE before, this older post covers the mechanics.

The four string types, and the “n”

There are two axes. The first is fixed versus variable length: char and nchar are fixed and padded to their declared width, while varchar and nvarchar store only what you put in them (plus a little overhead). The second axis is the n prefix, and that is the one that decides encoding.

Without the n, char and varchar are non-Unicode. Each stores one byte per character using the code page of the column’s collation, so a Latin1 collation gets you the 256 characters of Windows-1252 and nothing else. With the n, nchar and nvarchar are Unicode, encoded as UTF-16: two bytes per character for everything in the Basic Multilingual Plane, and four bytes (a surrogate pair) for supplementary characters like emoji.^[1]

One detail that trips people up: in varchar(40) and nvarchar(40), the 40 is not a character count. For varchar it is a byte count; for nvarchar it is a count of byte-pairs. With multibyte encodings, the number of characters you can actually fit can be smaller than the number you declared.^[2]

What UTF-8 collations changed

Starting with SQL Server 2019, a collation whose name ends in _UTF8 changes how char and varchar store data: instead of a single-byte code page, they use UTF-8 encoding, which covers the full Unicode range.^[3] UTF-8 is variable-width: one byte for the ASCII range (U+0000 to U+007F), two bytes up to U+07FF, three bytes up to U+FFFF, and four bytes for supplementary characters.

That is the part worth internalizing, because it cuts both ways. For data that is mostly ASCII, a UTF-8 varchar stores Unicode at roughly half the size of nvarchar. For data that is mostly in the three-byte range, such as Chinese, Japanese, or Korean text, a UTF-8 varchar is actually larger than nvarchar, because three bytes per character beats two. UTF-8 is a space win for Latin-heavy data and a space loss for East Asian data. Note also that the _UTF8 collation only affects char and varchar; the n types stay UTF-16 regardless.

Measuring it: the same text, three columns

To see this concretely, here is a table with the same string stored three ways: a non-Unicode varchar, a UTF-16 nvarchar, and a UTF-8 varchar.

Now four rows, each holding the same text in all three columns: an ASCII string, “café” (with a Latin-1 accented é), a three-character Japanese word, and a single emoji (a supplementary character). DATALENGTH returns the storage size in bytes, so we can compare directly:

The result, from a SQL Server 2019 instance:

Read down the columns and the whole story is there. For pure ASCII (row 1), UTF-8 ties the single-byte varchar at 3 bytes and beats nvarchar‘s 6. For “café” (row 2), UTF-8 needs 5 bytes because the é costs two, still under nvarchar‘s 8. For the Japanese word (row 3), UTF-8 balloons to 9 bytes against nvarchar‘s 6, the reversal I mentioned. And notice the non-Unicode v_latin column: for rows 3 and 4 it stored only 3 and 2 bytes, because it could not represent those characters at all and silently substituted question marks. That silent data loss is the real hazard of non-Unicode columns, quite apart from the byte counts.

Down to the bytes

DATALENGTH tells us the sizes; DBCC PAGE shows us the actual bytes. Here is the record for the “café” row, lifted from the page with dump style 3 (the annotations are the Database Engine’s own):

The three encodings of “café” sit right next to each other, and the letter é (Unicode U+00E9) is the tell:

• Non-Unicode varchar: 63 61 66 e9. The c, a, f are ASCII, and é is a single byte e9, its Windows-1252 code point. Four bytes.
• UTF-16 nvarchar: 63 00 61 00 66 00 e9 00. Every character is two bytes, little-endian, so é is e9 00. Eight bytes.
• UTF-8 varchar: 63 61 66 c3 a9. The ASCII letters are one byte each, and é becomes the two-byte UTF-8 sequence c3 a9. Five bytes.

The Japanese row makes the reversal impossible to miss. Its three characters store as e5 65 2c 67 9e 8a in the UTF-16 column (six bytes, two per character) but as e6 97 a5 e6 9c ac e8 aa 9e in the UTF-8 column (nine bytes, three per character). Same text, and UTF-8 is 50% larger.

Where the length actually lives

There is one more thing hiding in that byte dump, and it explains the “+2 bytes” you may have seen quoted for varchar. Look at the bytes right after the row header: 0017001f 0024. Those are the variable-column offset array. Every variable-length column in the record contributes a two-byte entry recording where its data ends: 0x0017 (23), 0x001f (31), 0x0024 (36). The engine finds each column’s start by looking at where the previous one ended, and its length by subtracting.

That array, plus a two-byte count of how many variable columns there are, is the real overhead of a variable-length column. It is why the documentation describes varchar(n) storage as “n bytes + 2 bytes” and nvarchar(n) as “2 times n bytes + 2 bytes”.^[2] The two bytes are that offset-array slot. Fixed-length char and nchar columns do not pay it, which is the trade you make for padding them out to full width.

Versions, and one collation caveat

Two version notes matter here. UTF-8 collations (the _UTF8 suffix) arrived in SQL Server 2019; on 2016 or 2017 your only Unicode option is nvarchar. Storing supplementary characters like emoji correctly in the n types needs a supplementary-character (_SC) collation, which has been available since SQL Server 2012; that is why the nvarchar column above uses Latin1_General_100_CI_AS_SC. Without an _SC collation, an emoji is still stored as its two byte-pairs, but the engine miscounts it as two characters instead of one.

The practical guidance falls out of the byte counts. If your text is predominantly ASCII or Western European and you want Unicode safety, a UTF-8 varchar can nearly halve your storage versus nvarchar. If your text is predominantly East Asian, nvarchar is the smaller choice and UTF-8 will cost you. And if you are still reaching for plain varchar with a single-byte collation to “save space,” measure what you are actually losing first, because the savings come with silent character substitution. For the related question of whether to just declare everything (max), I wrote about that separately.

What is the strangest string-storage surprise you have hit, a doubled byte count after a collation change, or a column quietly turning text into question marks? I would like to hear it. Find me on Bluesky or LinkedIn.

References

1. nchar and nvarchar (Transact-SQL) – Microsoft Learn. UTF-16 storage, byte-pairs, and surrogate pairs for supplementary characters. ↩
2. char and varchar (Transact-SQL) – Microsoft Learn. n is a byte count, the +2 byte variable-length overhead, and the UTF-8 collation behaviour. ↩
3. Collation and Unicode support: storage differences between UTF-8 and UTF-16 – Microsoft Learn. When UTF-8 is smaller than UTF-16 and when it is larger. ↩