SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation

A collation conflict means character expressions use incompatible comparison rules. I choose the intended rules before applying COLLATE.

The original comparison used two differently collated columns, but its JOIN syntax omitted an ON clause. This worked example uses French and SQL Latin collations. It deliberately produces the conflict and then resolves the expression comparison.

CREATE TABLE #A (name nvarchar(20) COLLATE French_CI_AS);
CREATE TABLE #B (name nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS);
INSERT INTO #A VALUES (N'Paris');
INSERT INTO #B VALUES (N'Paris');
BEGIN TRY
    EXEC(N'SELECT a.name FROM #A AS a JOIN #B AS b ON a.name = b.name;');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS expected_error, ERROR_MESSAGE() AS error_message;
END CATCH;
SELECT a.name FROM #A AS a
JOIN #B AS b ON a.name COLLATE DATABASE_DEFAULT = b.name COLLATE DATABASE_DEFAULT;
DROP TABLE #A;
DROP TABLE #B;

The first result reports the expected collation-conflict error. The second returns Paris. Dynamic SQL allows the compilation error to be caught without ending the entire teaching batch.

Choose semantics before the clause

DATABASE_DEFAULT uses the current database’s default collation. I use it when those comparison rules are intended. Case-sensitive, accent-sensitive and cross-database comparisons still require an explicit decision.

For a temporary-table character column that should match the user database, specify COLLATE DATABASE_DEFAULT when creating the column. Expression casts solve the local comparison; they don’t change the original column or database collation.

I review the plan and comparison behavior before changing a schema. Fixing one comparison doesn’t establish that every column needs a different collation.

Reference: COLLATE reference.

Related reading

• SQL SERVER – Parameter Sniffing Simplest Example
• SQL SERVER – Parameter Sniffing and Local Variable in SP
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
• Performance and Recompiling Query – Summary

Original video

COLLATE is not a choice of spelling, it is a choice of comparison rules.

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