Collation Precedence: Which Collation Wins in a Comparison

The join compares two text columns and fails before returning a row. Collation precedence explains which comparison rule wins and when no rule wins. Choose that rule deliberately instead of pasting a random COLLATE clause.

Collation Precedence Starts With Each Operand

I inspect both column definitions when a text join raises error 468. The database default alone doesn't explain the operands. Each column can have its own collation.

A collation controls comparisons such as case and accent sensitivity. Some collations also determine encoding behavior for varchar. Matching the character type doesn't guarantee matching comparison rules.

SQL Server classifies expressions with collation labels. The main labels are explicit, implicit and coercible default. No-collation describes an unresolved combination.

These labels aren't synonyms for the names of collations. A column created with an explicit COLLATE definition still has an implicit label when referenced. The expression-level clause determines the explicit label.

Use a disposable database for the examples. The sample tables contain conflicting case-sensitive and case-insensitive columns. The test is about comparison rules, not measured performance.

CREATE TABLE dbo.CollationLeftDemo
(ItemId int PRIMARY KEY, CodeText nvarchar(20) COLLATE Latin1_General_100_CI_AS NOT NULL);
CREATE TABLE dbo.CollationRightDemo
(ItemId int PRIMARY KEY, CodeText nvarchar(20) COLLATE Latin1_General_100_CS_AS NOT NULL);
INSERT dbo.CollationLeftDemo VALUES(1, N'abc');
INSERT dbo.CollationRightDemo VALUES(1, N'ABC');
SELECT OBJECT_NAME(c.object_id) AS TableName, c.name, c.collation_name
FROM sys.columns AS c
WHERE c.object_id IN (OBJECT_ID(N'dbo.CollationLeftDemo'), OBJECT_ID(N'dbo.CollationRightDemo'));

Let a Column Override a Default Literal

String literals and ordinary character variables have the coercible-default label. Their default collation comes from the relevant database context. A column reference has an implicit label.

Implicit takes precedence over coercible default. Comparing the case-insensitive column with a differently cased literal therefore uses the column's rule. It doesn't adopt the literal's database default automatically.

The first query below compares a literal against the left table. The second compares a variable against it. Both illustrate the same label relationship.

A stored procedure's declarations use its own database context for their default collation. An ad hoc batch uses the current database context. Keep that difference in mind during cross-database investigation.

Collation precedence is evaluated alongside data type conversion rules. Unicode and non-Unicode comparisons introduce another layer. Fixing a collation conflict doesn't settle every conversion cost.

SELECT ItemId, CodeText FROM dbo.CollationLeftDemo WHERE CodeText = N'ABC';
DECLARE @Code nvarchar(20) = N'ABC';
SELECT ItemId, CodeText FROM dbo.CollationLeftDemo WHERE CodeText = @Code;

Reproduce the Conflicting Column Join

Two different implicit collations don't get an automatic winner. Their combination receives no-collation. A collation-sensitive equality operation then cannot compare them successfully.

The join below intentionally demonstrates that failure. It runs through sp_executesql so the outer CATCH can display the lower-scope error. A same-scope compilation failure isn't reliably caught by that outer statement's CATCH.

The CATCH block returns error 468. Its message names both collations and the equal to operation.

The conflict isn't solved by making both tables live in one database. Their column definitions still differ. Nor does qualifying the table names choose a comparison rule.

I keep this small reproducer when deciding the correct business behavior. It makes the dispute visible without searching a large result. A silent sensitivity change can be worse than a clear error.

BEGIN TRY
    EXEC sys.sp_executesql N'
        SELECT l.ItemId, r.ItemId
        FROM dbo.CollationLeftDemo AS l
        JOIN dbo.CollationRightDemo AS r ON r.CodeText = l.CodeText;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Use Collation Precedence to Settle the Join

An expression with COLLATE has an explicit label. Explicit takes precedence over implicit. Apply the agreed collation to one operand to resolve the comparison.

The following versions deliberately ask different business questions. The first ignores case. The second distinguishes it.

The first query returns the matching pair. The second returns no rows, because abc and ABC differ by case. Choose between them from the identifier contract. Don't call the case-insensitive result more correct merely because it returns a match.

Applying conversion to an indexed column can affect access choices. Inspect the actual plan after resolving correctness. A normalized schema is preferable when the same mismatch appears across important queries.

Collation precedence explains why the clause wins. It doesn't choose the right rule for product codes, names or external identifiers. That remains a business and schema decision.

SELECT l.ItemId AS LeftItemId, r.ItemId AS RightItemId
FROM dbo.CollationLeftDemo AS l
JOIN dbo.CollationRightDemo AS r
  ON r.CodeText COLLATE Latin1_General_100_CI_AS = l.CodeText;
SELECT l.ItemId AS LeftItemId, r.ItemId AS RightItemId
FROM dbo.CollationLeftDemo AS l
JOIN dbo.CollationRightDemo AS r
  ON l.CodeText COLLATE Latin1_General_100_CS_AS = r.CodeText;

Recognize No-Collation in a CASE Expression

CASE can combine values from the two differently collated columns. Without another explicit rule, the combined expression has no-collation. A SELECT output or another collation-sensitive use can then fail.

The next example applies an explicit collation to the completed CASE expression. That provides the missing output rule. It isn't a change to either stored column.

No-collation is therefore an expression state, not a new selectable collation name. You can't fix it by looking for a collation called no-collation. Use an actual agreed comparison rule.

Two explicit operands with different collations also produce an error. Explicit doesn't mean SQL Server picks the first clause. Avoid conflicting clauses on both sides of the same comparison.

These distinctions help explain expressions that fail outside a join. Concatenation and set operations also deserve a collation review. Follow the contributing operands rather than blaming the last operator alone.

SELECT (CASE WHEN l.ItemId = 1 THEN l.CodeText ELSE r.CodeText END)
       COLLATE Latin1_General_100_CI_AS AS ChosenCode
FROM dbo.CollationLeftDemo AS l
CROSS JOIN dbo.CollationRightDemo AS r;

Check Temporary and Cross-Database Inputs

Temporary table character columns normally inherit tempdb's default unless you specify otherwise. A user database can have another default. That difference creates familiar join conflicts.

COLLATE DATABASE_DEFAULT on a temporary column can align it with the current user database. It still doesn't guarantee alignment with every source column. Inspect source definitions when columns override that default.

Cross-database joins require the same review. Neither database automatically wins because it appears first in the statement. The operands' labels and collations decide the result.

What comparison rule must this identifier follow across the application? Write it down before adding COLLATE. The repair should preserve that contract on every path.

I review uniqueness constraints after any proposed sensitivity change. Values previously distinct can become equal under another collation. Check the stored population before changing the schema.

Collation precedence turns a mysterious conflict into a traceable expression rule. Resolve the business semantics first, then inspect access and migration cost. Two rulers don't become one by arguing louder.

A comparison rule also belongs in tests for imports and temporary staging tables. Include mixed-case strings and accented characters when they represent real business values. A query that compiles still needs the intended equality behavior.

Related reading on this blog: Fixing Collation Conflicts Between tempdb and Your Database and UTF-8 Collations in SQL Server 2019: When They Save Space.

A collation fix is not an arbitrary clause, it is an explicit choice about how text values compare.

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