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
HI Mr. Pinal Dave, such a great help, the collation problem wasnt there o the query i sent earlier, bacause that was incomplete…
the complete query plus the solution:::
set @str = ‘select t0.docnum as SORefNo, t0.DocDueDate as SODocDueDate, t0.cardCode as CustomerCode, t7.cardName as CustomerName,
t1.itemcode as ItemCode, t4.ItemName as ItemName, t5.WhsName as WareHouseName, t6.U_NAME as UserName,
t1.quantity as SOQty, t2.quantity as SIQty,
case
when t1.quantity<=t2.quantity then 0 else t3.quantity
end as DraftSIQty, t1.quantity – t2.quantity as ”BackOrder”
from ordr t0 inner join
rdr1 t1 on t0.docentry=t1.docentry inner join
ocrd t7 on t0.CardCode=t7.CardCode inner join
ousr t6 on t0.Usersign=t6.UserId inner join
oitm t4 on t1.itemcode=t4.itemcode left join
owhs t5 on t1.Whscode=t5.Whscode left join
(select t10.draftkey, t11.baseentry, t11.baseline, t11.quantity from
oinv t10 inner join inv1 t11 on t10.docentry=t11.docentry) t2 on
t2.baseentry=t1.docentry and t2.baseline=t1.linenum left join
drf1 t3 on t3.baseentry=t1.docentry and t3.baseline=t1.linenum and (t2.draftkeyt3.docentry or t2.quantity is null)
where (t0.docduedate between ”’+convert(varchar(20),@startDate,102)+”’ and ”’+convert(varchar(20),@EndDate,102)+”’ ) ‘
— @whsCode
if ( @whsCode ‘NULL’ )
Begin
SET @str = @str + ‘ and ( (t1.Whscode COLLATE DATABASE_DEFAULT in (Select Value from fnSplitter(”’+ @whsCode +”’) ) ) or (t1.Whscode COLLATE DATABASE_DEFAULT is null) )’
End
Else
Begin
SET @str = @str + ‘ and((t1.Whscode in (t1.Whscode)) or (t1.Whscode is null))’
End
SET @str = @str + ‘ and not(t2.quantity is null and t3.quantity is null) ‘
SET @str = @str + ‘ order by docnum ‘
exec (@str)
Mr.Pinal, ur such a genius, i want to be ur friend.. u may not be aware but u helped me already with a lot of things, ur articles was so useful…..thanks thanks for the solution… :D:D:D
Thanks a million for the blog post Pinal.
Very usefull stuff.
:)
Jean
Thanks a lottttttt!!!!! It worked!
HI Mr. Pinal Dave,
How can we change the collation of a column???
what r the different types of collation in MS Sql??
Please help me asp.
Thanks in advance…
HI Mr. Pinal Dave,
I want to find the list of those employees whose name starts with either A or B without using union query.
Thanks Pinal,
Saved me a lot of searching.
Jason ;-)
Could you please help, i get the collation error with this query:
SELECT *
FROM ctbl_ReplenishOrders RO
left JOIN #tbl_OutputOrdered OO ON RO.OrderDate = OO.OrderedDate AND RO.Itemcode COLLATE DATABASE_DEFAULT = COLLATE DATABASE_DEFAULT OO.ItemCode
WHERE OO.Itemcode IS NULL
Even though i am trying your collation solution, it seems still not to work. Can you please help?
Many thanks!marlene
wow, u r wonderful dude
PD – thanks for the post!
Marlene , try:
SELECT *
FROM ctbl_ReplenishOrders RO
left JOIN #tbl_OutputOrdered OO ON RO.OrderDate = OO.OrderedDate AND RO.Itemcode COLLATE DATABASE_DEFAULT = OO.ItemCode COLLATE DATABASE_DEFAULT
WHERE OO.Itemcode IS NULL
Thanks Pinal,
It works.
Excellent this worked perfectly. Thanks!!!
Very useful tip. Thanks, perfect answer!
Thanks. I had problem with temporary table in join and this was nice and easy fix!
Thanks so much for the tip! It was very helpful.
Gracias, salvaste mi dia XD.
Thank you saved my day XD.
Thankyou , really helped me.
It’s works! you kick ass dude
Thanks Pinal, that saved the day for me!
John
Hi dave….awesome….a solution made to look so simple for quite a complex problem (it was definetly complex for me…:)…thanks a ton
Very helpful! Thank you…