SQL Server raises a collation conflict when an operation compares character expressions that use incompatible sorting and comparison rules. Identify the two collations before applying a query-level fix or changing schema.
Last updated: September 26, 2026.
SELECT a.CustomerCode, b.CustomerName
FROM SalesA.dbo.Customers AS a
JOIN SalesB.dbo.Customers AS b
ON a.CustomerCode COLLATE DATABASE_DEFAULT
= b.CustomerCode COLLATE DATABASE_DEFAULT;COLLATE DATABASE_DEFAULT converts both expressions to the current database’s default for this comparison. An explicit named collation is better when the intended case and accent behavior must be fixed regardless of execution context.
Inspect the actual collations
Check database defaults in sys.databases and column collations in sys.columns. A database default affects newly created columns; changing it does not rewrite existing column definitions. Also check data types: mixing varchar code pages can lose characters, while nvarchar stores Unicode.
Choose the smallest safe fix
- Use COLLATE in one query for a deliberate cross-database comparison.
- Define temporary text columns with COLLATE DATABASE_DEFAULT when tempdb differs.
- Standardize column collations during a planned schema migration when conflicts are widespread.
- Avoid changing the server collation merely to repair one query.
The COLLATE reference explains expression-, column-, database-, and server-level behavior. Index use may be reduced when SQL Server must convert a join expression at runtime, so examine the execution plan for large tables.
Test comparison semantics
Case sensitivity, accent sensitivity, supplementary characters, and code pages are business rules, not just syntax. Test values that differ by case or accents before standardizing. MySQL reports a similar class of problem but uses different character-set rules; use the dedicated MySQL collation conflict guide there.
