Migrating SQL Server to UTF-8 Collations Without Breaking Data
In SQL Server 2019 and later, varchar columns using a collation whose name ends in _UTF8 can store Unicode text, so not every text column has to become nvarchar. The change is more than a single database setting, though: comparisons, sorting, unique indexes and storage sizes all shift. The article lays out a step-by-step path from column inventory to production rollout.
Key points
- UTF-8 collations are available in SQL Server 2019 and later.
- VARCHAR(n) is measured in bytes, so column sizes need recalculating for multi-byte characters.
- Changing the database default collation does not convert existing columns automatically.
- New comparison rules can make unique indexes reject values they previously accepted.
- Build an inventory of text columns through sys.columns before migrating.
- Take backups and measure log growth, locking and application behavior before production rollout.
A UTF-8 collation makes char and varchar columns encode text as UTF-8, so scripts beyond a single-byte code page fit without forcing a move to nvarchar. The trap is size: VARCHAR(200) still means 200 bytes, not 200 characters, so multi-byte values that used to fit may now overflow. Measure real lengths with DATALENGTH and LEN before altering anything.
Changing the database default collation alone does not rewrite existing columns, which keep their own settings. The author recommends inventorying text columns through sys.columns, then testing a column-level ALTER COLUMN against realistic multilingual data. Unique indexes can reject rows the old rules accepted, and joins across columns with mismatched collations may raise conflicts. Large tables need room in the transaction log and a staged, carefully synchronized rollout.
Real-world use
- A multi-tenant SaaS database whose customer name and address fields must hold non-Latin characters.
- An e-commerce team storing product descriptions from external suppliers in varchar columns and trying to cut storage costs.
- A DBA and .NET team planning a staged schema change to keep production downtime low.
Why it matters
Moving to UTF-8 can improve storage and multilingual support, but untested index and comparison behavior risks silent data errors in production.