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
thanks dude!
You are awsome. Thank you for your tips. This saved me on an error issue.
Another query I found useful to find the differences in my two collation types is:
select * from fn_helpcollations()
where name = ‘SQL_Latin1_General_CP1_CI_AS’
or name = ‘Latin1_General_CI_AS’
Hi i need a help with collation problem
I am creating a database with sql_latin_general collation in an sql instance with collation latin_general.
While running installed application error occurs.is there any way to handle this scenario.
Please help me its very urgent
Great help thanks.
I am doing a join on a block of data created with a select and a view. As far as I can see there is no way to set the collation on the columns so you would expect them to the database default anyway.
Your solution gets it going.
Thanks again
I’ve been running queries from a local db server against a remote db server joining tables between the two. We moved our databases to new hardware and now the remote server gives us the collation error when we run the queries where it never did before. All the databases were restored so the db environment should be exactly the same, any idea why it would start generating these errors with just a hardware change?
I tried changing the “collation compatible” setting on the remote server from false to true but then the connection failed. Isn’t there some server setting that would fix this for me?
Thanks a bunch!
Thank you. This is exactly the type of thing you don’t think about until it hits you unexpectedly.
Muchas Gracias por la informacion.
Very Helpful
thanks.
Hi
I’ve been running queries from a local windows mobile device (SqlClient on device) against a remote db server (SQL 2005) We have a Table which has two Collations one is Default one is Chinese_PRC_BIN (column name is called name). When I using following SQL select string SELECT supplier_code,name FROM code_relations WHERE name COLLATE DATABASE_DEFAULT ”, It return NullreferenceException. If I only select supplier_code it works fine.
Please give me help!
Thanks Pinal.
This helped me a lot and saved a lot of my time.
Great, Thanks so Much.
Thanks for this, much appreciated
Great stuff! Thanks. A one-line fix that saved hours of woe and searching.
@Klaus: is there a copy & paste issue or the where clause is incomplete?
SELECT supplier_code,name
FROM code_relations
WHERE name COLLATE DATABASE_DEFAULT
Cheers mate!
Thank you.
My problem is solved..
Hi – don’t understand where this error suddenly appeared from but your fix work liked a charm thankyou
it is working perfectly. thanks for the very useful post.
thank you. I had a problem with case and union that is solved like this.
the error was on a nvarchar column
CASE WHEN c1= 1 THEN c2_nvarchar COLLATE DATABASE_DEFAULT
ELSE c3_nvarchar COLLATE DATABASE_DEFAULT