Cannot resolve collation conflict for equal to operation.
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)
381 Comments. Leave new
thank you very muck!
good luck!
Thanks for this wonderful trick….
thank you broo
Dear sir,
i am muthukumar..i have a error when restoring databae in sql server 2005.And i hava taken the database backup in sql2008.
The error message is
The media family on device ‘G:\myproject\Insurance.BAK’ is incorrectly formed. SQL Server cannot process this media family.
VERIFY DATABASE is terminating abnormally.
please help me…
This can not be restored. You can restore the backup that were taken by earlier or current versions only
There is very little to say after Alastair Vize’s “Pinal Dave – You are an international treasure.” May be only THANK YOU.
Pinal please help me simplify
In ragards to XXX_CI and XXX_CS do I undestand correctly that with XXX_CI “Three = ThREE” would be true. Respevtively with XXX_CS “three = ThRee” it would be false and only “Three = Three” it would be true?
Also is this true in the cases of IF compare, Where clause, Join predicates etc.
If the collation is XXX_CS, then “three=”ThRee” becomes false. This is application if you compare with anystring with that collation in IF statement also
madhivanan – Thank youuuuuu. I could have tried to see it in practice but I would never be sure if this makes it a rule
Thanks a lot!!!
Pinal, Please, please take a look at my question from Dec 30. and see if you can help me. You can just use yes or no 3 times and that would be all.
Thanks It works for me
Great, many thanks!!
Pinal thank you very much, it really works, not just for each field, but it also works simply adding like a sentence at the end of the query. Greettings from Caracas-Venezuela
Thanks it worked.
Thanks…
Pradip killedar
Kolhapur
Hi pinaldave,
Am getting the error which mentioned below while executing some plsql,
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the UNION operation.
So, what to do?. Please help me..
Thanks Pinaldave, very helpful!!!
MuchÃsimas gracias. TenÃamos ya varios años con éste problema y gracias a tà lo pudimos resolver definitivamente
thank you!
Thanks. It was indeed very helpful.
thank you , it saved my time a lot.
Thanx Pinal, its really helpful.
The !funny thing about SQL Server is that it reports collation error even if statements like this one:
IF @recId IS NULL AND @labelId IS NULL
…
…