SQL Server: How to troubleshoot collation conflict errors when executing queries
![](/site/assets/files/1611/grey_image_blog-1.webp?v=1711557869)
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:
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.