SQL Server: How to troubleshoot collation conflict errors when executing queries

In Microsoft SQL Server, the collation can be set at the column level. When you compare (or concatenate) two columns having different collation in a 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

Example 1:

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

Example 1 fixed:

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

Example 2:

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

Example 2 fixed:

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

Example 3:

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

Example 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 a SELECT clause, in a comparison, in a WHERE clause, and in a 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.