“Cannot resolve the collation conflict” in SQL Server

When you try to compare values between column that have different collations, you will get the following error:

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot_resolve_the_collation_conflict

We tried to compare values in PersonType columns in two tables: Person and PersonNew:

USE AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person p
INNER JOIN Person.PersonNew p1
ON p.PersonType = p1.PersonType

To check collation of of the PersonType column of the Person.Person table:

USE AdventureWorks
GO
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Person'
AND TABLE_NAME = 'Person'
AND COLUMN_NAME = 'PersonType'

person_person_persontype_collation

We can see that the collation of the PersonType column of the Person.Person table is SQL_Latin1_General_CP1_CI_AS.

To check collation of of the PersonType column of the Person.PersonNew table:

USE AdventureWorks
GO
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Person'
AND TABLE_NAME = 'PersonNew'
AND COLUMN_NAME = 'PersonType'

person_personnew_persontype_collation

Collation of the PersonType column of the Person.PersonNew table is SQL_Latin1_General_CP1_CS_AS.

To resolve the collation conflict, you can add COLLATE DATABASE_DEFAULT clause to compare values using database collation of the current database:

USE AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person p
INNER JOIN Person.PersonNew p1
ON p.PersonType = p1.PersonType COLLATE DATABASE_DEFAULT

Or you can specify a collation that will be used for comparing the values:

USE AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person p
INNER JOIN Person.PersonNew p1
ON p.PersonType = p1.PersonType COLLATE SQL_Latin1_General_CP1_CI_AS

If the post helped you, please share it:
Pin It

1 comment to “Cannot resolve the collation conflict” in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">