Fix SQL Server Collation Conflicts Without Changing Meaning

A join that works on one SQL Server can fail after a database restore to another instance because the two compared text expressions have different collations. Adding COLLATE until the error disappears may restore execution while changing which rows match. Case sensitivity, accent sensitivity, and sort order are part of the data contract.

Start by asking what the identifier means. Should customer codes abc and ABC identify the same customer? Should an accented name compare equal to an unaccented name for a search, but remain distinct in stored presentation? A technical conflict cannot be resolved correctly until the intended comparison is clear.

Inspect the levels that actually participate

Server, database, and column settings are related but are not interchangeable.

SELECT SERVERPROPERTY('Collation') AS ServerCollation,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation,
       DATABASEPROPERTYEX(N'tempdb', 'Collation') AS TempdbCollation;

SELECT name AS ColumnName, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Customers')
  AND collation_name IS NOT NULL;

Replace dbo.Customers with the table in the failing query. Individual character columns can differ from the database default. A restored database retains its own settings, while ordinary temporary-table columns often inherit tempdb's default on conventional SQL Server installations. This explains why a procedure can fail only after moving servers.

Unicode types do not eliminate collation rules. nvarchar changes how characters are represented, but comparisons still need a collation. In this small example, the comparison rule changes the result.

SELECT
 CASE WHEN N'Cafe' COLLATE Latin1_General_100_CI_AI = N'café'
      THEN 1 ELSE 0 END AS InsensitiveMatch,
 CASE WHEN N'Cafe' COLLATE Latin1_General_100_CS_AS = N'café'
      THEN 1 ELSE 0 END AS SensitiveMatch;

InsensitiveMatch is 1 and SensitiveMatch is 0. The first comparison ignores both case and accents; the second distinguishes them. Neither result is universally correct. A product search and a uniqueness constraint may legitimately need different rules.

Use Unicode literals and correctly typed parameters when handling multilingual input. A later COLLATE cannot restore characters already lost by conversion through an incompatible non-Unicode code page. Fix the ingestion and parameter contract before treating every garbled value as a sorting problem.

Align the staging boundary

When the permanent target column uses the current database default, a temporary staging column can explicitly use that same default.

-- Suitable when the target column uses the current database default.
CREATE TABLE #Incoming (
    CustomerCode nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
);
CREATE INDEX IX_Incoming_Code ON #Incoming(CustomerCode);

This avoids accidentally inheriting an unrelated tempdb collation. It is not a universal solution: if the target column has an explicit nondefault collation, the staging column should match that actual column and its business semantics. Verify the database context in which the temporary object is created.

For an occasional cross-database query, an explicit COLLATE on an expression may be reasonable. Choose the rule deliberately and inspect the actual plan. Applying a different collation to a large indexed column can require conversion and prevent straightforward use of its existing ordering. Aligning a smaller staging input and indexing it can be a better repeatable boundary.

Do not wrap every comparison in LOWER or UPPER as a general substitute. That can add per-row work, complicate index access, and still fail to express the desired accent and language behavior. If normalized lookup keys are part of the design, store or compute them under one explicit rule and enforce that contract consistently.

Treat a collation migration as a data change

Changing a database's default does not automatically rewrite the collation of existing user-table columns. A real migration must inventory affected columns, indexes, constraints, computed expressions, and cross-database consumers. Deployment scripts and new-object defaults need to agree with the resulting design.

Check for values that become equal under the destination rule before rebuilding a unique index. Rows differing only by case or accents may collapse into one logical key. Resolve those collisions through an approved mapping; do not arbitrarily keep the first row and discard the other customer's relationships.

Sort order can change pagination and exports as well as equality joins. Use a unique tie-breaker in ordered results, and test representative multilingual values rather than only ASCII examples. Include mixed case, accents, empty strings, and the actual identifier formats your application accepts.

Finally, validate both results and cost. Compare matched identifiers, unmatched staging rows, and duplicate counts, then examine reads and the execution plan. A successful query with a new collation is only a syntactic repair until the intended relationships and performance have also been checked.

Technical references: Microsoft Learn: Collation and Unicode · Microsoft Learn: COLLATE.