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
I just ran in to this collation issue during a data transfer. My Google search had this “old” article as the top result.
Great article, got me out of a jam. Thank you very much!
thank you very much
hi Ilike it very much, I hope you will soon update your work!thanks for sharing
Thanks so much!
tank so much man:)
Enormous!
Thanks very much.
Thanks u so much man :)
Hi,
I have the following query –
CREATE VIEW MappedObjects
AS
SELECT
EM.EntityID AS ID,
EM.EntityType AS EntityType,
ParentEntityID AS ParentID,
EM.EntityGUID AS GUID, EM.[Name] ,
ES.StoreID AS StoreID
FROM EntityMaster AS EM WITH (NOLOCK) LEFT JOIN EntityStore AS ES WITH (NOLOCK) ON ES.EntityID = EM.EntityID AND ES.EntityType COLLATE DATABASE_DEFAULT = EM.EntityType COLLATE DATABASE_DEFAULT
UNION ALL
SELECT TP.TopicID AS ID, ‘Topic’ AS EntityType,0 AS ParentID, TP.TopicGUID AS GUID, TP.[Name], TS.StoreID AS StoreID
FROM Topic AS TP WITH (NOLOCK) LEFT JOIN StoreMappingView AS TS WITH (NOLOCK)
ON TS.EntityID = TP.TopicID AND TS.EntityType =’Topic’
UNION ALL
SELECT NW.NewsID AS ID,’News’ AS EntityType,0 AS ParentID, NW.NewsGUID AS GUID, NW.Headline AS [Name], NS.StoreID AS StoreID
FROM News AS NW LEFT JOIN StoreMappingView AS NS WITH (NOLOCK)
ON NS.EntityID = NW.NewsID AND NS.EntityType =’News’
UNION ALL
SELECT PR.ProductID AS ID, ‘Product’ AS EntityType,0 AS ParentID, PR.ProductGUID AS GUID, PR.[Name], PS.StoreID AS StoreID
FROM Product AS PR LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON PR.ProductID = PS.EntityID AND PS.EntityType =’Product’
UNION ALL
SELECT CP.CouponID AS ID, ‘Coupon’ AS EntityType,0 AS ParentID, CP.CouponGUID AS GUID, CP.[CouponCode] AS [Name], PS.StoreID AS StoreID
FROM Coupon AS CP LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON CP.CouponID = PS.EntityID AND PS.EntityType =’Coupon’
UNION ALL
SELECT OO.OrderOptionID AS ID, ‘OrderOption’ AS EntityType,0 AS ParentID, OO.OrderOptionGUID AS GUID, OO.[Name], PS.StoreID AS StoreID
FROM OrderOption AS OO LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON OO.OrderOptionID = PS.EntityID AND PS.EntityType = ‘OrderOption’
UNION ALL
SELECT GC.GiftCardID AS ID, ‘GiftCard’ AS EntityType,0 AS ParentID, GC.GiftCardGUID AS GUID, GC.SerialNumber AS [Name], PS.StoreID AS StoreID
FROM GiftCard AS GC LEFT JOIN StoreMappingView AS PS WITH (NOLOCK)
ON GC.GiftCardID = PS.EntityID AND PS.EntityType = ‘GiftCard’
It gives me an error as –
Msg 451, Level 16, State 1, Procedure MappedObjects, Line 4
Cannot resolve collation conflict for column 5 in SELECT statement.
Please advise!!
I get collation error
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Japanese_BIN” in the equal to operation.
on my below query
declare @var varchar(max)
select @var = ‘Exec sp_change_users_login’
+ ”” + ‘auto_fix’+””+’,’+””+ name+”” +char(13)+ coalesce(@var,”)
from sysusers where name in
(select name from master..syslogins) order by name
Print @var
Exec(@var)
GO
Couldn’t get head around how to resolve the conflict in
select code from suppliers where suppliercode in (select code from #tempsuppliers)
tried
select code collate database _default from suppliers where suppliercode in (select code from #tempsuppliers collate database_default)
…but that just threw a syntax error. after a flash of inspiration realised that:
select code collate database _default from suppliers where suppliercode in (select code collate database_default from #tempsuppliers )
was the fix, this worked a treat, so thanks for all your tips!
You sick, maaaan! I know you’re the SQL Master!
What SQL problems which you can’t resolve?
even this “simple but iritating” problem, you know the answer! Good job!
It works nice also in INNER JOINS in ON clause
Thanx
Thanks for posting this! You saved me hours of frustration and research. :)
Thanks pinnal,
This solution save my lot of time and job..
Tahnk you once again
How to resolve the collation problem in the update query?
Thanks 4 this….
thanks, this really works
Another Pinaliscious solution. Thanks!
Thanks for your tipp! It saved my day.