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 i need a help with collation problem
I am creating a database with sql_latin_general collation in an sql instance with collation latin_general.
While running installed application error occurs.is there any way to handle this scenario.
Please help me its very urgent
Thanks, very helpful!!!
Exactly what I needed, thanks.
Very tidy, saved me from my ALTER TABLE nightmare against an 30Gb db
Thanks!
Thanks !!!
This help me with this …
WHERE ESTADO_PROCESO COLLATE DATABASE_DEFAULT in (select string from dbo.fn_SplitStrings(@as_estados, ‘,’))
Thank you very much! Nice tip!
Worked like a charm, thanks man :)
Many Many Thanks :)
It worked out for me!!!!!!!!!!!!
Plz kip up the gud work.
Thanks, it works!
thank you baby
Hi guys
I have added the COLLATE DATABASE DEFAULT after my join clauses, now i get an error: Expression type int is invalid for COLLATE clause.
Here is the query, can someone please help??
SELECT
I.Name,
A.Name EventShortName,
ISNULL(F.Name, ”) GroupShortDisplay,
C.ParameterName,
D.WinningValue,
COUNT(EntrantID) TotalEntries,
CASE WHEN D.WinningValue = G.Pick THEN
COUNT(EntrantID)
ELSE
0
END CorretEntries,
CASE WHEN D.WinningValue G.Pick THEN
COUNT(EntrantID)
ELSE
0
END IncorrectEntries
FROM [Pikum.Web].dbo.Event A WITH (NOLOCK)
INNER JOIN [Pikum.Web].dbo.[EventParameter] B WITH (NOLOCK)
ON B.EventID = A.EventID COLLATE DATABASE_DEFAULT
INNER JOIN [Pikum.Web].[dbo].[Parameter] C WITH (NOLOCK)
ON C.ParameterID = B.ParameterID COLLATE DATABASE_DEFAULT
INNER JOIN [Pikum.Web].dbo.[PikumEventParameter] D WITH (NOLOCK)
ON D.EventParameterID = B.EventParameterID COLLATE DATABASE_DEFAULT
LEFT JOIN [Pikum.Web].dbo.EventCategoryGroup E (NOLOCK)
ON E.EventID = A.EventID COLLATE DATABASE_DEFAULT
AND E.EventCategoryGroupID = D.EventCategoryGroupID COLLATE DATABASE_DEFAULT
LEFT JOIN [Pikum.Web].dbo.CategoryGroup F (NOLOCK)
ON F.CategoryGroupID = E.CategoryGroupID COLLATE DATABASE_DEFAULT
INNER JOIN [Pikum.Web].dbo.[EntrantPick] G WITH (NOLOCK)
ON G.PikumEventParameterID = D.PikumEventParameterID COLLATE DATABASE_DEFAULT
INNER JOIN [Pikum.Web].dbo.Pikum I WITH (NOLOCK)
ON I.PikumID = D.PikumID COLLATE DATABASE_DEFAULT
WHERE PikumStatusID >= 7
AND UserPikumCreatorID = 1566
and pikumidentifier = ‘V7Y2A-D4F40’
GROUP BY
I.Name,
A.Name,
ISNULL(F.Name, ”),
C.ParameterName,
D.WinningValue,
G.Pick
Thank you!
worked like a charm, thanks….
Thanks, very helpful!!!
Correct and Simple solution.
Good Work.
thanks
Can one of theese errors hang and a restart the SQLServerAgent service?
Thanks – Great Solution
Thanks… It solved my problem