UTF-8 Collations in SQL Server 2019: When They Save Space

A mostly English text column moved to NVARCHAR for Unicode support can take twice the bytes for its common values. UTF-8 collations in SQL Server 2019 offer a VARCHAR alternative with Unicode storage, but savings depend on the characters. Measure your data before changing the column.

What UTF-8 Collations Change in Storage

NVARCHAR stores text using UTF-16, while VARCHAR under a supported _UTF8 collation stores UTF-8 bytes. Plain English letters generally use one byte in UTF-8 and two in UTF-16. Many European characters use more than one UTF-8 byte. Many common Asian characters use three UTF-8 bytes and two UTF-16 bytes. The mix in your table decides whether the column shrinks or grows.

I start with a sample of real values, including names, punctuation, emoji, and the longest strings. A test with only ABC makes UTF-8 look universally brilliant. It is not. What languages and symbols does the application actually store, and what does the client expect to receive?

Build Two Tables With the Same Text

Use a SQL Server 2019 or later test database that supports the _UTF8 collation. The script creates temporary tables with matching values. One uses NVARCHAR and one uses VARCHAR with a UTF-8 collation. N-prefixed literals preserve Unicode before conversion into the target column. The chosen collation is an example; choose sensitivity rules that match the application.

CREATE TABLE #Text16
(
    RowID int NOT NULL PRIMARY KEY,
    SampleText nvarchar(100) NOT NULL
);
CREATE TABLE #Text8
(
    RowID int NOT NULL PRIMARY KEY,
    SampleText varchar(100)
      COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL
);
INSERT #Text16 (RowID, SampleText)
VALUES (1, N'Plain English'),
       (2, N'Café résumé'),
       (3, N'東京のデータ');
INSERT #Text8 (RowID, SampleText)
SELECT RowID, SampleText FROM #Text16;

For a production comparison, load a representative large sample rather than three rows. Preserve the same data and row count in both tables. A VARCHAR length is a byte limit under UTF-8, not a promise of that many Unicode characters. A value that fits NVARCHAR(100) can fail to fit VARCHAR(100) after conversion.

Compare Bytes for Each Language

DATALENGTH reports storage bytes for the value. LEN counts characters with its own trailing-space behavior, so it is not the measure for this test. Join matching rows and compare byte counts side by side. The three examples show why a single average hides the language mix. NULL and trailing spaces should be part of a real migration sample too.

SELECT a.RowID, a.SampleText,
       DATALENGTH(a.SampleText) AS utf16_bytes,
       DATALENGTH(b.SampleText) AS utf8_bytes
FROM #Text16 AS a
JOIN #Text8 AS b ON b.RowID = a.RowID
ORDER BY a.RowID;

Do not read the result as a guaranteed table-size reduction. Row headers, null bitmap, variable-length offsets, page fill, and indexes add space. Compression can change the result again. I calculate total DATALENGTH by language group, then compare page counts after loading a realistic sample. The value-level test tells you why the result moved.

Compare Pages After a Real Load

For page counts, fill both test tables with the same representative rows and inspect allocation. sys.dm_db_index_physical_stats can report page_count for the clustered index in tempdb. Small three-row tables mostly show fixed overhead, so do not draw a storage conclusion from them. Use thousands of rows with the same mix as production, then compare reserved and used pages as well as logical reads for typical queries.

SELECT object_id, index_id, page_count,
       avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#Text8'),
    1, NULL, N'DETAILED'
);

Run the same function for #Text16. The script is a measurement pattern, not a claim that the sample rows fill enough pages to compare. If data compression is enabled in production, reproduce it in the test. A smaller column can still produce similar page counts when fixed costs dominate.

Check Index Keys Under UTF-8 Collations

A UTF-8 VARCHAR key uses bytes, and long non-ASCII values can push an index key toward its size limit. Inspect the longest encoded values, index definitions, and included columns before altering the type. Collation also defines comparison and sort behavior. Joining a UTF-8 column to a column with the old collation can require an explicit COLLATE expression or produce a conflict. That can change seekability and results.

I test equality, ordering, case sensitivity, accent sensitivity, and joins against the real application queries. A collation change is a semantic change, not just a storage knob. It can affect unique keys when two values compare equal under the new rules. Run a duplicate check under the target collation before rebuilding a unique index. The deployment plan needs room for index rebuilds and rollback.

Verify Client Driver Behavior

Applications and drivers must send and read Unicode values correctly. Check the driver version, parameter types, ORM mapping, bulk-load path, and exports. A client that still sends text through a legacy code page can damage data before SQL Server stores it as UTF-8. Test round trips with accented, Asian, emoji, and combining characters. Compare bytes and rendered text after the application reads them back.

Do not assume that changing one database default collation converts existing columns. Existing columns keep their own definitions until changed. A staged migration of one representative column is easier to validate than a database-wide flip. Keep backups and a rollback plan, and check downstream reports and ETL before declaring success.

Check how the driver binds string parameters. An NVARCHAR parameter compared with a UTF-8 VARCHAR column can introduce conversion work and change the chosen index path. Capture an actual application call and inspect the plan for implicit conversions. Bulk copy and import tools deserve a separate round-trip test because their encoding defaults can differ from interactive queries. Include malformed input and strings near the declared byte limit. A successful single-row insert from SSMS is only the start of the client test.

Decide on UTF-8 Collations From the Real Mix

UTF-8 can save space for mostly ASCII text while preserving Unicode storage. NVARCHAR can remain smaller for text dominated by characters that take three UTF-8 bytes. Neither encoding is a universal winner. Record the data mix, byte totals, page counts, index sizes, query plans, and client tests together.

I also check growth over time. A product that starts with English-only data can become multilingual. Design for the expected future range, not just last week's sample. The right answer is the one that stores every character correctly and meets the measured storage and query goals.

Related reading on this blog: Change Database and Table Collation and Varchar vs Nvarchar: Storing Non-English Characters: SQL in Sixty Seconds #126.

UTF-8 is not a universal space saver, it is a storage choice to test against real text and clients.

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