SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation

June 11, 2007

Cannot resolve collation conflict for equal to operation.

SQL SERVER - Cannot Resolve Collation Conflict For Equal to Operation collationconflict-800x206

In MS SQL SERVER, the collation can be set at the column level. When compared to 2 different collation columns in the query, this error comes up.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add the following keywords around “=” operator.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Watch this quick video for resolution:

Collation can affect the following areas:

Where clauses
Join predicates
Functions
Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Checkout following video how collation can change the order of the column:

Just like collation, there is another problem with SQL Server and that is related to Parameter Sniffing, I have written a detailed blog post about how the parameter sniffing can be resolved in SQL Server.

Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.

SQL SERVER – Parameter Sniffing Simplest Example
In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
SQL SERVER – Parameter Sniffing and Local Variable in SP
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
Performance and Recompiling Query – Summary
This post summarizes the entire series of parameter sniffing, performance and recompiling query.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Best Practices, SQL Collation, SQL Error Messages, SQL Joins, SQL Scripts, SQL Server

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – LEN and DATALENGTH of NULL Simple Example

381 Comments. Leave new

Marvin Schmidt
April 27, 2012 5:08 pm
Hey Pinal
I works, but i have issues with danisk special characters like æ ø å they get converted to char like ‘ > †
the databases are using Danish_Norwegian_CI_AS SQL 2008 and the other is SQL_Scandinavian_CP850_CI_AS SQL 2005
Can you help…
Reply
Elizabeth
May 7, 2012 9:23 pm
Easiest online solution I’ve ever found. Thanks!
Reply
mohan
May 8, 2012 3:46 pm
thanx
Reply
Paulo
May 15, 2012 6:10 pm
That was really helpful! Thanks a lot!
Reply
Giuseppe Knezovich
May 29, 2012 12:24 pm
You made some nice points there. I looked on the internet for the subject and found most people will consent with your blog.
Reply
Paulo Cornélio
June 6, 2012 9:59 pm
superb
Reply
glutenfreesql
June 9, 2012 12:07 am
Thanks
Reply
rajkumar
June 11, 2012 5:24 pm
Great…….It worked for me.
Reply
marcoa_escalante
June 12, 2012 9:41 pm
excellent !!
Reply
jibson
June 18, 2012 11:23 am
Hey pinal,
Thanks for the solution
Reply
Dejan Miklavcic
June 19, 2012 7:58 pm
You are a genious :D !!!!!
Reply
Nitin Vijay
June 30, 2012 4:28 pm
Hello Pinal,
Thanks for the Solution….
Reply
GC Koch
July 13, 2012 1:15 am
Re: Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “German_PhoneBook_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
It may be of interest the “best practice” numeric sql construct (also on joins) avoids collation conflicts between user-defined and system databases. Example: the following numeric “in” statement is successful between the German_PhoneBook_CI_AS and SQL_Latin1_General_CP1_CI_AS collations:
select DatabaseName from VersionDef
where VersionCode = N’10.0.1.4540′
and DatabaseSID in (
select database_id
from sys.databases
where name = DB_NAME()
)
Reply
Manoj kumar
July 26, 2012 11:50 am
Hi All,
One of our mappings in ODI generating code which will hit MS Sql 2008. I am getting the same error. In my case, I can not change the code generated by ODI tool. I have to make sure that the two tables/columns have the same collation.
How can i achieve that without interrupting any other databases/data ?
Could you help asap.
Reply
TUSHAR RANE
August 17, 2012 11:28 am
IA AM GETTING ERROR WHILE RUN FOLLOWING STATEMENT
UPDATE Kept_Report_Master
SET [DESCRIPTION]=DISP_TAB.[DESCRIPTION],
DISP_CLASS=DISP_TAB.CLASS
FROM Kept_Report_Master
INNER JOIN DISP_TAB
WHERE Kept_Report_Master.DISPOSITION_CODE = DISP_TAB.DISP_CODE
Reply
michele
August 23, 2012 12:37 am
Thank you very much. You save my time again!!
Reply
Alex
August 24, 2012 2:14 pm
Thanks very much!
Reply
Maria J. Santos
September 5, 2012 6:53 pm
Thank you. Your help is always apreciated :)
Reply
sanitabh soni
September 20, 2012 3:04 pm
Thank You, It was very informative.
Reply
Alastair Vize
September 26, 2012 2:20 pm
Pinal Dave – You are an international treasure. Once again you get me out of a tight spot with a quick, reliable and straightforward solution. I am (and not for the first time) very much in your debt. Abundant thanks.
Reply