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
it really helped me a lot. Thank you sooooooooo much…
Thank you soo much. Your post is really helped me. Once again thank you very much.
Regards,
Praveen
Thanks a lot.. that’s a great help.
Chan
Thanku so much.. :)
sorry I have a column which works under chinese collate I am getting this error
“Cannot resolve collation conflict between ‘Latin1_General_CI_AS_KS_WS’ and ‘Chinese_PRC_Stroke_CI_AI_KS_WS’ in equal to operation”
after using the COLLATE DATABASE_DEFAULT it returns the row count as zero even thoug there are some chinese values inside it.Is there any work around for this please help.
many thanks,
Balaji
Kardeş allah razı olsun ya :) Seni Seviyoruz..
Special Thanks
Thank You So Much. We Love You.
Hi
I am tring to create a view with tables from 2 different databases, these databases have different collation
i have used collate function but it gives me this error
Msg 447, Level 16, State 0, Line 1
Expression type int is invalid for COLLATE clause.
here is the select statement from my view
SELECT DOCTYPE,
DOCNUM,
GROSS = CAST(ABS(SUM(CASE WHEN NOMINAL = 45110 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
VAT = CAST(ABS(SUM(CASE WHEN NOMINAL = 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
NET = CAST(ABS(SUM(CASE WHEN NOMINAL != 45110 AND NOMINAL != 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2))
FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE LIKE ‘SL%’)
OR (DOCTYPE LIKE ‘Y%’)
GROUP BY
DOCTYPE,
DOCNUM
COLLATE SQL_Latin1_General_CP1_CI_AS
UNION
SELECT DOCTYPE,
DOCNUM,
GROSS = CAST(ABS(SUM(CASE WHEN NOMINAL = 45110 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
VAT = CAST(ABS(SUM(CASE WHEN NOMINAL = 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2)),
NET = CAST(ABS(SUM(CASE WHEN NOMINAL != 45110 AND NOMINAL != 54005 THEN VALUE ELSE 0 END)) AS DECIMAL(12,2))
FROM Dreamliv.SQSDBA.D_DETAILS
WHERE (DOCTYPE LIKE ‘SL%’)
OR (DOCTYPE LIKE ‘Y%’)
GROUP BY
DOCTYPE,
DOCNUM
Please help.
@All:
hii ppl I have resolved the chinese collation successfully. I use the Prefix N’ ‘ while inserting, updating and selecting values. By this way I am able to get the exact chinese characters without any issues.
Example:
SELECT * FROM Messages where Message=N’隐私权政策’
INSERT INTO Messages (Message) VALUES (N’隐私权政策’)
thanks to one and all!!
It’s working, great job. :)
thx
its work
many thanks
Also, when doing a UNION with tables from different databases/collation, note that the COLLATE properties of all text fields must match. To do this, add a COLLATE statement after each text field that is coming from the foreign table to the result set. UNION will then be able to compare text from each unioned query.
For example, if the foreign table has collation Latin1_General_CP1_CI_AS and the default database has SQL_Latin1_General_CP1_CI_AS, do this in the query of the foriegn table:
SELECT x.field COLLATE SQL_Latin1_General_CP1_CI_AS
FROM foreigntable x
Adding a COLLATE statement after each text field in the foreign table will ensure the UNION will be able to merge queries from disparate collating sequences.
Thank you. I used this to create a view from different databases in SQL Server 2008
its work
many thanks
Very good.
Thank you
Thanks dear it really solved my problem…
Thanks a lot Dear,
You are always a great help. because ur examples are very streigtforward and to the point.
Thanks.
Regards,
Zeeshan.
hi,
i have a problem and says “Cannot resolve collation conflict for equal to operation”. i am using Windows Server 2008 R2 and a SQL server 2000. when i see the collation of instance it was Latin1…. but when i look the collation of databse it was only SQL_Latin1… how i can change the collation of instance because i need to change it into SQL_Latin1…?
i really need your help. thank you very much.
I have a query as follows:
I have two tables, are of same design and residing in two different database server and of different collations
Can I use except & Intercept commands and to compare the results
For e.g..
Select field1 collate database_default, field2 collate database_default, field3 collate database_default….fieldn collate database_default from table1
except
Select field1 collate database_default, field2 collate database_default, field3 collate database_default….fieldn collate database_default from [dbserver].[dbname].dbo.table2
Yes you can use those to find out a difference
Please let me know where should i use Collation clause i m getting error in the select statement, Could not resolve collation conflict for column 1 in the above query.
SELECT
SalesChannel,
NoOfTransactions,
TotalTickets,
GrossAmount
FROM(
SELECT
tra.new_saleschannelidname SalesChannel,
count(tra.new_transactionid) As NoOfTransactions,
sum(tra.new_numberoftickets) As TotalTickets,
sum(tra.new_grossticketvalue) As GrossAmount
FROM NEWTRANS tra
WHERE tra.deletionstatecode = 0 and new_saleschannelidname is not null
and tra.new_vistabookingstatus = ‘P’
–and tra.new_bookingcommitted = 1–‘Yes’
–and tra.new_paymentprocessed = 1–‘Yes’
and tra.new_saleschannelidname not like ‘LOYALTY%’
and CAST(Convert(CHAR(10),
dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) >= ‘{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) = ‘{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,itmtra.new_transactiondate),121)AS SMALLDATETIME) ='{From_Date}’
and CAST(Convert(CHAR(10),dateadd(mi,330,tra.new_transactiondatetime),121)AS SMALLDATETIME) <='{To_Date}'
–and DATENAME(yyyy,dateadd(mi,330,tra.new_transactiondatetime)) = DATENAME (yyyy,getdate())
GROUP BY tra.new_saleschannelidname
)tra
ORDER BY SalesChannel
Dear Jeffrey
The solution you have introduced solved my problem completely.
Thank you for it
SELECT DISTINCT col1
FROM sale
WHERE (col2 IN
(SELECT id COLLATE DATABASE_DEFAULT
FROM linkedServer.DB1.dbo.tbl1))