SQL SERVER – How to troubleshoot collation conflict errors when executing queries

In MS SQL SERVER, the collation can be set at the column level. When you compare (or concatenate) 2 differents collation column in the query, this error occurs.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CI_AS” in the equal to operation.

How to know the collation for a specific column name?

Type this query :

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SC.collation AS "Collation" 
FROM dbo.sysobjects SO 
INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U'  and SC.NAME = 'ColumnName'
ORDER BY SO.[name], SC.colid 

Solution 1: Fix the query

Sample 1 :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 = T2.Col2

Sample 1 fixed :

SELECT T1.*, T2.*
FROM Table1 T1, Table2 T2
WHERE T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 2 :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2

Sample 2 fixed :

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 COLLATE DATABASE_DEFAULT = T2.Col2 COLLATE DATABASE_DEFAULT

Sample 3 :

SELECT T1.Name + T2.Name 
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id

Sample 3 fixed :

SELECT T1.Name COLLATE DATABASE_DEFAULT + T2.Name COLLATE DATABASE_DEFAULT
FROM Table1 T1 
INNER JOIN Table2 T2 ON T1.Id = T2.Id 

As you can see, COLLATIONS can affect a query, in a concatenation in the SELECT clause, in a comparison in WHERE clause, and JOIN clause.

Solution 2: Fix the collation on the column

Query :

ALTER TABLE T1
ALTER COLUMN Col1 COLLATE SQL_Latin1_General_CP1_CI_AS

(OR DATABASE_DEFAULT for example)

The goal is to harmonize COLLATIONS in this solution.