Changing a SQL Server database to use UTF-8 can make it easier to store multilingual text in varchar columns without converting every text column to nvarchar. However, changing a collation is not simply a matter of updating one database setting.
Existing applications may depend on how strings are compared, sorted, indexed, or stored. A migration can introduce unexpected character conversions, change query results, or cause unique indexes to reject values that were previously accepted.
The safest approach is to understand how SQL Server collations work, identify the columns that need UTF-8 support, and test the migration before applying it to production.
What Is a UTF-8 Collation in SQL Server?
A collation defines how SQL Server compares and sorts character data. It can control characteristics such as case sensitivity, accent sensitivity, and linguistic sorting behavior.
SQL Server supports UTF-8 encoding for char and varchar data when a compatible collation is used. These collations have names ending in _UTF8.
For example:
SELECT
SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
SERVERPROPERTY('Edition') AS SqlServerEdition;
UTF-8 collations are available in SQL Server 2019 and later. Confirm that your SQL Server version and environment support the features you plan to use.
A UTF-8 collation allows a varchar column to store characters from across Unicode, provided the column has sufficient capacity and the application handles the data correctly.
Consider this example:
CREATE TABLE dbo.Customers
(
CustomerId INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(200)
COLLATE Latin1_General_100_CI_AS_SC_UTF8
);
The column uses a UTF-8 collation. It can store many languages without requiring an nvarchar column.
However, VARCHAR(200) means a maximum of 200 bytes, not necessarily 200 characters. Some UTF-8 characters require multiple bytes, so the number of characters that fit depends on the data.
UTF-8 vs. NVARCHAR
SQL Server's nvarchar type uses UTF-16 encoding. The varchar type normally uses a code page determined by its collation, but a UTF-8 collation changes its encoding to UTF-8.
The choice depends on your application's requirements and existing schema.
Feature | VARCHAR with a UTF-8 collation
| NVARCHAR
|
|---|
Character encoding | UTF-8 | UTF-16 |
Unicode support | Yes | Yes |
Storage per character | Variable, depending on the character | Usually two bytes for a UTF-16 code unit |
Storage for supplementary characters | Up to four bytes per character | Typically a surrogate pair using four bytes |
Column size interpretation | Bytes | Byte pairs, generally corresponding to two bytes per UTF-16 code unit |
Compatibility | Requires a compatible UTF-8 collation and application support | Widely used for Unicode text in SQL Server |
UTF-8 can reduce storage requirements for data dominated by ASCII characters. However, it is not guaranteed to save space for every language or dataset.
For example, ordinary English letters generally use one byte in UTF-8, while many other characters require two, three, or four bytes. Measure the actual data before making storage or performance assumptions.
Why Changing a Collation Can Be Risky
A collation migration can affect more than character encoding.
Existing data may require more bytes
Suppose an existing column uses a single-byte code page. A character that was previously represented by one byte may require multiple bytes in UTF-8.
A column that was previously large enough for a particular value may no longer have enough capacity for the same number of characters.
For example:
CREATE TABLE dbo.ProductDescriptions
(
ProductId INT PRIMARY KEY,
Description VARCHAR(100)
);
After moving to UTF-8, a description containing many multilingual characters may require more than 100 bytes.
The column definition might need to change:
ALTER TABLE dbo.ProductDescriptions
ALTER COLUMN Description VARCHAR(300)
COLLATE Latin1_General_100_CI_AS_SC_UTF8;
This is only an illustrative example. Before changing a column, inspect its constraints, indexes, dependencies, and existing data. The new size must reflect actual requirements rather than an arbitrary increase.
String comparisons may change
Collations determine how strings compare. A change can affect case sensitivity, accent sensitivity, sorting, and equality comparisons.
For example, a case-insensitive collation can treat Admin and admin as equal for comparison purposes.
If a migration changes comparison behavior, queries and unique indexes may behave differently.
Before changing a collation, identify whether the existing application depends on a particular comparison rule.
Unique indexes may fail
Imagine a table contains two values that were considered distinct under the old collation but become equal under the new one.
A unique index may no longer be valid under the new comparison rules.
For this reason, test unique constraints and indexes before changing production data. Do not assume that an existing unique index will remain valid simply because the stored strings appear different.
Step 1: Inventory Existing String Columns
Start by identifying the character columns in the database.
The following query lists columns that use char, varchar, nchar, or nvarchar:
SELECT
s.name AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
ty.name AS DataType,
c.max_length AS MaxLengthBytes,
c.collation_name AS CollationName,
c.is_nullable AS IsNullable
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.object_id = t.object_id
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.types AS ty
ON c.user_type_id = ty.user_type_id
WHERE ty.name IN
(
'char',
'varchar',
'nchar',
'nvarchar'
)
ORDER BY
s.name,
t.name,
c.column_id;
This inventory helps identify columns that may need attention.
Pay particular attention to columns containing names, addresses, product descriptions, customer messages, imported data, or text from external systems.
Do not convert every column automatically. Some columns may intentionally use a specific collation or may not need UTF-8 support.
Step 2: Check the Existing Collation
Before changing anything, inspect the database's current collation:
SELECT
name AS DatabaseName,
collation_name AS DatabaseCollation
FROM sys.databases
WHERE name = DB_NAME();
The database default collation affects new objects and certain operations, but it does not automatically change every existing column's collation.
Existing character columns can retain their own collation settings. Temporary tables, table variables, and expressions involving columns with different collations can also introduce compatibility problems.
A UTF-8 migration should therefore distinguish between changing the database default and changing individual columns.
Step 3: Identify Values That May Be Affected
Before changing encoding, inspect representative data and determine whether it can be represented correctly.
A query such as the following can help estimate the storage used by a varchar column:
SELECT
ProductId,
DATALENGTH(Description) AS StoredBytes,
LEN(Description) AS CharacterCount
FROM dbo.ProductDescriptions;
DATALENGTH reports the number of bytes used by a value. LEN counts characters but excludes trailing spaces. For supplementary Unicode characters, interpretation also depends on the data type and collation.
For a more accurate migration assessment, copy representative data into a test environment and compare the stored values before and after conversion. Include accented characters, non-Latin scripts, supplementary characters, and values near the column's size limit.
Do not rely only on a few English-language records.
Step 4: Test a Column-Level Migration
A column-level change is often easier to control than changing the default collation for an entire database.
For example:
ALTER TABLE dbo.Customers
ALTER COLUMN CustomerName VARCHAR(200)
COLLATE Latin1_General_100_CI_AS_SC_UTF8
NOT NULL;
The example assumes that the column is defined as NOT NULL and that its existing constraints and dependencies permit the alteration.
Use the actual nullability and complete column definition from your schema. Some changes may require dropping and recreating dependent indexes or constraints, and large tables may need significant time and transaction-log space.
After the change, inspect the column definition:
SELECT
c.name AS ColumnName,
ty.name AS DataType,
c.max_length AS MaxLengthBytes,
c.collation_name AS CollationName
FROM sys.columns AS c
INNER JOIN sys.types AS ty
ON c.user_type_id = ty.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Customers')
AND c.name = 'CustomerName';
Then verify the data using known test cases and application-level queries.
Step 5: Test Queries, Indexes, and Application Behavior
A successful ALTER COLUMN statement does not prove that the application will continue working correctly.
Test the areas most likely to be affected:
Queries that compare strings or use ORDER BY.
Unique indexes and constraints.
Joins between character columns with different collations.
Stored procedures and functions that manipulate text.
Temporary tables and staging tables.
Search features that use LIKE, CHARINDEX, or other string operations.
Applications that assume a fixed number of bytes per character.
For example, the following query may expose a collation conflict when the two columns use incompatible collations:
SELECT
c.CustomerId,
o.OrderId
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerCode = o.CustomerCode;
If a conflict occurs, inspect the column collations and determine the correct comparison behavior. Applying COLLATE directly in a query can be useful in specific cases, but it should not become a substitute for a deliberate schema design.
Step 6: Plan the Production Migration
For a production database, treat a collation change as a schema migration with measurable operational risk.
A practical migration plan should include:
Back up the database and confirm that the backup can be restored.
Inventory affected columns, indexes, constraints, and dependent objects.
Test the proposed changes against a recent copy of production data.
Measure migration duration, transaction-log usage, and locking behavior.
Check application compatibility, including drivers and data-access libraries.
Schedule the migration according to the impact on users.
Validate stored values, query results, and application behavior after deployment.
Prepare a tested recovery plan.
Changing a column's collation can require schema changes and index maintenance. For large tables, consider a staged migration using a new column, data conversion, validation, and a controlled application cutover.
A staged migration requires extra care with writes during the transition. If applications continue updating the old column while data is being copied, the new column can become inconsistent unless the migration includes a reliable synchronization strategy.
Common Mistakes to Avoid
Assuming UTF-8 always reduces storage
UTF-8 can save space for many ASCII-heavy datasets, but characters from other scripts may require more bytes. Compare actual storage requirements before making decisions.
Changing only the database default
Existing columns retain their own collations. Review and migrate the columns that actually need to change.
Ignoring unique constraints
Different comparison rules can cause previously distinct values to compare as equal. Test unique indexes before migration.
Treating encoding and sorting as the same thing
UTF-8 describes how characters are encoded in varchar data. A collation also defines comparison and sorting behavior. Choose a collation that matches both encoding and application requirements.
Skipping application tests
Database storage may be correct while application code still makes incorrect assumptions about character lengths, byte counts, or string comparisons.
Best Practices
Use these practices when planning a UTF-8 migration:
Begin with a small set of columns that have a clear Unicode requirement.
Keep database backups and migration scripts under change control.
Use representative multilingual test data.
Check index and constraint compatibility before altering columns.
Keep encoding, comparison, and sorting requirements explicit.
Measure storage and query behavior rather than assuming performance improvements.
Validate the migration in a staging environment before production deployment.
Document any columns that intentionally retain a different collation.
Summary
SQL Server UTF-8 collations allow varchar columns to store Unicode text using UTF-8 encoding. This can be useful for applications that need multilingual support and may reduce storage for some datasets.
However, changing a collation can affect storage requirements, string comparisons, unique indexes, and application behavior. The safest migration starts with a schema inventory, tests representative data, validates dependent objects, and uses a staged rollout when the risk warrants it.
The goal is not simply to change a collation name. It is to ensure that existing data remains correct and that queries and applications continue behaving as expected.
Join the conversation! Your thoughts help the community grow.