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 sir, you save my day ^^
Great solution from the original post… Thanks a lot
Thank you very much! It is a great help!
Cheers mate! :o)
Hi have the below query and cannot quite figure out where excatly to use the collation.
Can somebody please help:
INSERT INTO CRMSUBSCRIPTIONSV1_HITS(USER_LOGONNAME, USER_PASSWORD, ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED)
SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_ISHITBASED = ‘1’ AND NOT EXISTS
(SELECT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
FROM CRMSUBSCRIPTIONSV1_HITS,CRMSUBSCRIPTIONSV1 WHERE
CRMSUBSCRIPTIONSV1.USER_LOGONNAME = CRMSUBSCRIPTIONSV1_HITS.USER_LOGONNAME AND
CRMSUBSCRIPTIONSV1.USER_PASSWORD = CRMSUBSCRIPTIONSV1_HITS.USER_PASSWORD AND
CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID = CRMSUBSCRIPTIONSV1_HITS.ORDER_ORDERPRODUCTID AND
CRMSUBSCRIPTIONSV1.ORDER_HITSPURCHASED = CRMSUBSCRIPTIONSV1_HITS.ORDER_HITSPURCHASED)
GROUP BY ORDER_ORDERPRODUCTID,USER_LOGONNAME,USER_PASSWORD,ORDER_HITSPURCHASED
Thanks in advance.
Works!!!!!!!!!!!1
Thanks you very much sir. You’re the man!
Great! Worked!
Thank you very much…
Thank you.
Thanks Pinal!
I would like to add that use Collate with ‘IN’ clause also.
Have a look in my Query which I solved using collate for ‘IN’ clause.
SELECT DISTINCT top 100 PERCENT FT_TBL.product, FT_TBL.RSV_desc, FT_TBL.LIST_PRICE, FT_TBL.uom, KEY_TBL.RANK, PDoc.product AS PDocProduct,
FT_TBL.pgroup, FT_TBL.Special_Conditions, FT_TBL.shop_id, SUM([Order Contents].Qty) AS Sqty
FROM Products1 AS FT_TBL INNER JOIN
FREETEXTTABLE(Products1, *, ‘fuel pump’) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] LEFT OUTER JOIN
[Order Contents] ON FT_TBL.product COLLATE DATABASE_DEFAULT = [Order Contents].[Item Number] COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN
(SELECT product
FROM ProductDocs
WHERE ([Shop ID] IS NULL)) AS PDoc ON FT_TBL.product COLLATE DATABASE_DEFAULT = PDoc.product COLLATE DATABASE_DEFAULT
WHERE (FT_TBL.shop_id IS NULL) AND (FT_TBL.car IN
(select DISTINCT [Car Type] FROM [Car Types]))
GROUP BY FT_TBL.product, FT_TBL.RSV_desc, FT_TBL.LIST_PRICE, FT_TBL.uom, KEY_TBL.RANK, PDoc.product, FT_TBL.pgroup, FT_TBL.Special_Conditions,
FT_TBL.shop_id
ORDER BY KEY_TBL.RANK DESC
thanks you very much for nice tips! Its save my day. :)
thanks you very much for nice tips!
(Ovo je bila nocna mora dosad – Hvala (Serbian) )
Thanks you very much, Greate.
Thanks Pinal.
It has helped me a lot.
YES !!!! THANK YOU VERY VERY MUCH
MERCI BEAUCOUP (French)
MECI EN PIL (FWI Creol)
:-D
Superb boss!!!!!!! thank u for ur knowledge sharing.
Thank you very much!
Its really works
Thank you very much
Thnaks for this Article. Really it helped me.