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
Thanks a lot!!!!!
Thanks. Worked well.
Someone know a way for change the collation in all tables and collumns, I migrated several Oracle Databases to SQLServer 2005 and a .net application in many servers with the same configutation but specially now in one server gave me collation errros. In my .net project I’m having several problems with collation in most querys. Thanks in advance.
Thank you — it worked GREAT!
Mr. Dave for President!!
Works fine!! THANKSSSSSSSS!!
Thank you Mr.Pinal
Great….
Thanks………it solved my problem in less than a minute
i appreciate
Thanks a Lot , it really solved My problem at Last Stage of Deployment
Regards
Sidiq
[Email removed]
Thank you so much, I have found this solution for a long time!
THX a lotttttttttttttttttttt :-x
Thank you for the solution.
very good blog thanks for web..
very nice posted thanks
Great small work around…
Thanks for all your helpful posts.
Thanks a lot.
This is very helpfull~~
Thanks a lot. I tried using the COLLATE keyword and it just did not work, I thought it wasn’t possible. Now I know that my problem was really just the syntax. LOL.
Thanks you – you solved my problem and I am getting home to my kids…..
Hi Pinal,
I have the following error:
Can’t resolve collation conflict between “Latin1_GeneralCI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the union operation. Below is the SQL statement.
SELECT 2000000000 + ASCII(RIGHT(RDB_SETTING, 1)) AS ID, ‘.’ + LEFT(RDB_VALUE, 25) AS NAME, SUBSTRING(RDB_SETTING, 6, 1) AS PRIORITY, NULL
AS ANALYSIS, NULL AS RECOVERY, 1 AS RESTASK_CHANGE_COUNT, ‘UNAVAILABLE’ AS INCIDENT_ID, RDB_VALUE AS Remedy_Summary, NULL
AS ASSIGNEE, ‘Field Support’ AS Assignee_Group, ‘Commercial Ltd’ AS Company, NULL AS Contact, NULL AS Notes, NULL AS Address, NULL
AS PhoneNumber, ‘Busy’ AS Status
FROM dbo.RDB
WHERE (RDB_SECTION = ‘priority’) AND (RDB_SETTING LIKE ‘Alias%’) AND (RDB_SETTING ‘Alias’ + RDB_VALUE)
UNION
SELECT 2000000000 + Retain_ID AS ID, LEFT(Full_Name, 25) + ‘-UNAVAILABLE’ AS NAME, ‘4’ AS PRIORITY, NULL AS ANALYSIS, NULL AS RECOVERY,
1 AS RESTASK_CHANGE_COUNT, ‘UNAVAILABLE’ AS INCIDENT_ID, ‘Planned Unavailable’ AS Remedy_Summary, LEFT(Full_Name, 25) AS ASSIGNEE,
‘Field Support’ AS Assignee_Group, ‘Commercial Ltd’ AS Company, NULL AS Contact, NULL AS Notes, NULL AS Address, NULL AS PhoneNumber,
‘Busy’ AS Status
FROM ARSystem.dbo.CTM_People
WHERE (Support_Staff = 0) AND (Client_Type BETWEEN 1000 AND 5999) AND (Assignment_Availability = 0) AND (Profile_Status = 1) AND
(Department = ‘Service’)
UNION
SELECT CAST(RIGHT(a.Task_ID, 12) AS int) AS ID, LEFT(a.Company + ‘-‘ + a.RootRequestID + ‘-‘ + a.Task_ID, 128) AS NAME, LEFT(a.Priority, 1) AS PRIORITY,
a.Retain_Analysis AS ANALYSIS, a.Retain_Recovery AS RECOVERY, a.RESTASK_CHANGE_COUNT, a.RootRequestID AS Incident_ID, LEFT(a.Summary,
254) AS remedy_summary, LEFT(a.Assignee, 50) AS Assignee, LEFT(a.Assignee_Group, 50) AS Assignee_Group, a.Location_Company AS COMPANY,
a.First_Name + ‘ ‘ + a.Last_Name AS CONTACT, CAST(a.Notes AS varchar(255)) AS Notes,
b.Street + ‘, ‘ + b.City + ‘, ‘ + b.State_Province + ‘, ‘ + b.Zip_Postal_Code AS ADDRESS, a.Customer_Phone_Number AS PhoneNumber,
a.zStatus AS Status
FROM ARSystem.dbo.TMS_Task AS a LEFT OUTER JOIN
ARSystem.dbo.SIT_Site AS b ON a.Site = b.Site
WHERE (a.Schedule_in_Retain = 1) AND (a.Status <= 6000) AND (a.StatusReasonSelection 3000 OR
a.StatusReasonSelection IS NULL)
Are you able assist with resolving this conflict issue, can the same rule be applied as it is a slightly different error message?
Many Thanks in Advance,
Khalil
Hi,
I have now fixed this, thanks for presenting the solution to me.
Best regards,
Khalil