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
Thnaks so much :))))))))))))))))))))))))))))
I know nothing about databases and SQL servers. I have a biometric time clock for my business and in the course of trying to install it on a new computer(I have previously installed it on at least 3 other computers) I got this error message-
“Cannot resolve collation conflict for equal to operation”. The company that sells the time clock says that the problem is with the SQL server and they can’t help me. I read the first post but it assumes you have a clue and I don’t. Would someone be able to walk me through this? I build and repair computers for myself and my business and install and use lots of software but this is way over my head. I am hesitant to even ask about a tutorial because it seems to me that it would take forever to learn enough to be able to figure this out. Any suggestions would be greatly appreciated.
Thanks for looking, Bill
Was the first hit on Google and solved my problem. Thanks a lot!
It really helped me. Thanks for your help
Thanks……These all really helpful.
Thanks
delete from procstatus with (rowlock) where
empcd in(select m.empcd from procstatus m with (nolock)
inner join billcontrol b with (nolock) on m.updatedby =b.preparedby and m.updatedon=b.preparedon and b.cancelled =’Y’ ” where(m.updatedby = procstatus.updatedby And m.updatedon = procstatus.updatedon))”
While executing this query this error occuring.
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Pls Help………
I love you Pinal Dave!
Pinal,
it is true that this would be one way to solve the failing query, but the questions is: why are the columns with different collations? Is this a design feature, or is it a mistake?
And if we change the collation of the columns ‘on the fly’ in our query, then are we going to get the correct resultset?
Think about it – the collation is a set of rules by which the data is treated (recognized, sorted, distinguished) and if we force the query to make the collations of the join collumns equal, then will this affect the resultset?
Check this article, and let me know that you think:
Feodor
Thank you, works nicely
And here is a good example why COLLATE database_default is NOT a good solution to the collation conflict problem:
thank you for using simple examples. two answers in two days!
thankuuuuuuuuuuuuuuuuuuuuuu………….. its nice
I had a query with a union of tables both of which are from the same collation. Worked fine in 2005. Now that I have migrated to 2008, the same query is failing. Any ideas?
What was the error you got?
I would like to add that while this may work on a single server with heterogeneously collated databases, it *may not* work for all collation regimes, or across servers with different collation regimes selected as the default type.
In this case it is possible to generate a maintenance script that combs through the various DBs and Tables on a server and alters the collation type of the text type data contained therein.
In this scenario it can take some time to correct the issue either by updating the existing tables (expensive transaction), or by creating a backup version of the existing tables, and scripting them to be recreated with the correct collation, then reinserting the table content into the new correct table (cheaper transactions individually, but may add up to be more costly than an update).
The better solution would be to change the default collation on the odd server, which requires some down time as well.
Plan accordingly. Our scenario involves a single server collated one way with all the others collated (uniformly) in a different regime. Unfortunately this server is a primary reference and we cannot afford the downtime to correct it… we run the maintenance procedure over night to correct any new table content created that day, which is fairly effective for us.
It works.
Thank you very much.
Wow!! Great, this really helps me a lot…
Even I am facing a error like this: Cannot resolve collation conflict for column 1 in SELECT statement.
My querry is SET @allItems = ‘SELECT CAST(iu.item_id AS varchar(max))+”/”+iu.description+”/I”+”/”+CAST(it.sequence AS varchar(max))+”/”+it.item_feature_parents as itemVal FROM ifl_item_u_Syn as iu INNER JOIN
#item_ids as it ON it.item_id= iu.item_id
Sorry, Im confused….
I have taken a working SP from 2005 into 2008. Made no changes.
I have the problem between a tempory table and a function call in the ON clause of a JOIN.
Now the function was created in the database, and so was the tempory table, so although your solution works, I dont understand where the problem has come from.
Please can you explain how two things created in the same database can get this problem? Thanks
thanks a lot…. it works…….
Didn’t get that?