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!! It really works!!
Thanks for the solution
thanks for that! it really helped me
Thanks a lots for simple and easy answer.
Agreed. Very helpful.
Grerat!
Thanks a lot. Its very helpful to resolve the problem.
Thanks a lot. That was quick and simple answer.
Thank’s! The Best Solucion!
Thanks for the help, I am still having a problem though maby i am being stupid. in my case i have a union select. see example
select top 10 ‘Listings’ as SearchItem , id as SearchResultID ,’Listings.aspx’ as PageToLoad ,Description as SearchDetail from StudentDirectoryItems
where Description like ‘%’ + @SearchValue + ‘%’ or Address like ‘%’ + @SearchValue + ‘%’ or PhoneNumbers like ‘%’ + @SearchValue + ‘%’ and VarsityID = @VarsityID
union
select top 10 ‘News’ as SearchItem , News.id as SearchResultID ,’HomePageNews.aspx?newsID’ as PageToLoad , News.ShortDescription as SearchDetail from News
left outer JOIN VarsityNews ON News.ID = VarsityNews.NewsID
where News.ShortDescription COLLATE DATABASE_DEFAULT like ‘%’ + @SearchValue COLLATE DATABASE_DEFAULT + ‘%’ or News.DisplayDescription COLLATE DATABASE_DEFAULT like ‘%’ + @SearchValue COLLATE DATABASE_DEFAULT + ‘%’ and VarsityNews.VarsityID = @VarsityID
union
Your help will be much appreciated.
Hi Pinal
i have the following query:
SELECT
Site.Site__ID,
Site.Site_Type,
Site.Site_Post_Code,
Site.Site_Town,
Site.Site_PhaseName,
Site.Site_Name,
Office.Office__ID,
Office.Office_Type,
Office.Office_Status,
Office.Office_Name,
[User].User_Organisation,
[User].User_PhaseName,
[User].User_FullName,
[User].User_EmailAddress
FROM Site WITH (NOLOCK)
LEFT JOIN
Site_SiteToOffice ON Site.Site__ID = Site_SiteToOffice.Site__ID
LEFT JOIN
Office ON Site_SiteToOffice.__Office__ID = Office.Office__ID
LEFT JOIN
Office_OfficeToUser ON Office.Office__ID = Office_OfficeToUser.Office__ID
LEFT JOIN
[User] ON Office_OfficeToUser.__User__ID = [User].User__ID
LEFT JOIN
Site_SiteToUser ON Site.Site__ID = Site_SiteToUser.Site__ID AND [User].User__ID = Site_SiteToUser.__User__ID
where
(len (@CompanyName_key)=0 OR Office_Name COLLATE DATABASE_DEFAULT like @CompanyName_key )
AND (len (@UserName_key)=0 OR User_FullName COLLATE DATABASE_DEFAULT like @UserName_key )
AND (len (@UserEmail_key)=0 OR User_EmailAddress COLLATE DATABASE_DEFAULT like @UserEmail_key )
AND (len (@SiteName_key)=0 OR Site.Site_Name COLLATE DATABASE_DEFAULT like @SiteName_key )
Although i have placed the collate database_deafault i still get the error? any suggestions?
Put the ‘Collate Database_Default’ in front of the column names. Like Column _name Collate Database_Default. The error will be resolved.
neddless to say that the parameters where decalred earlier on etc.
Thanks! It worked
thanks!
Chingón!
Very Helpful….thanks.
This work , very helpful
Thanks
Hi!!!
How can i use the COLLATE DATABASE_DEFAULT in this update
update a set area = b.area from tbl_lnventas as a
inner join [10.105.162.223].dbunica3.dbo.cat_entidad as b on b.codigolada = left(a.ctelefono,8)
where a.area=”
go
Great help! I ran into an issue with a case statemen and found an answer that might be helpful to someone else. Looked up “Collation Precendence” in books online and found a quick solution to a problem I don’t have time to fix the root cause for:
SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE DATABASE_DEFAULT
FROM TestTab
Thanks a lot!