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 Pinal.. Great stuff…
Thanks @Satish
Thanks Pinal! I’m not SQL server specialist and have problems with some SQL servers features often. And almost every time when I’m pasting the error into google – it leads me to your blog. Thank you!
your welcome @Denis
Thank you Mr Pinal, your post help me
Thanks Mr. alfredosilitonga
Thank you so much!
your welcome Gurpinder. I am glad you liked it.
In my example, it not is possible:
Im trying a join, with two tables, in two distinct databases, but it fails.
Example:
use databaseA
go
create table tableA (fieldcompare varchar(9) collate SQL_Latin1_General_CP1_CI_AS)
use databaseB
go
create table tableB (fieldcompare char(9) collate SQL_AltDiction_CP850_CI_AI)
insert tableA select ‘001’
insert tableB select ‘001’
the select above dont return values:
select *
from
databaseA..tableA a
join databaseB..tableB b
on a.fieldcompare COLLATE DATABASE_DEFAULT = b.fieldcompare COLLATE DATABASE_DEFAULT
In my example, it not is possible:
Im trying a join, with two tables, in two distinct databases, but it fails.
Example:
use databaseA
go
create table tableA (fieldcompare varchar(9) collate SQL_Latin1_General_CP1_CI_AS)
use databaseB
go
create table tableB (fieldcompare char(9) collate SQL_AltDiction_CP850_CI_AI)
insert tableA select ‘001’
insert tableB select ‘001’
the select above dont return values:
select *
from
databaseA..tableA a
join databaseB..tableB b
on a.fieldcompare COLLATE DATABASE_DEFAULT = b.fieldcompare COLLATE DATABASE_DEFAULT
Thanks
Thanks.. it worked :)
Hi,
Is there any way to find that which SQL server Collation required during intallation.
Like I have a DB which has Collation “Latin1_General_Bin2” Now I want to Install MS SQL Server which Collation is required for that.
During installation there is a screen where you can specify the collation needed.
Thanks Dave it really helps.
My pleasure saikumar439!
HI
i am getting below error . kindly help me
Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “Latin1_General_CI_AS” in the UNION operation.
Thanks a lot!!!!
I am getting this error “Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Could not use view or function ‘dbo.sysdac_instances’ because of binding errors. (.Net SqlClient Data Provider)”
Thank you so much ,thank a lot
Thank you very much right on the money.
How to resolve this error when both the fields are numeric values in JOIN clause. I am getting this error trying to run an SSIS package SQL Execute Task in a simple UPDATE statement on a table joining memory table bases on numeric IDs.
The post ist over 10 years old but still extremely helpful. Saved me a lot of time and I learned something new today! Thanks!
update employee_induction_details set MBRI_ID=Y.[Branch_ID] from uploads..Employees_Route_Code_sep Y
join employee_induction_details V on V.[eii_emp_code]=Y.[Emp_Code]
error is: Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation.
Please help me
Thanks!
You Saved the day again for me my friend Pinal :-)
Worked for me too :-)