Welcome back!
World is becoming increasingly global and so, for this reason, we are increasingly dealing with SQL Server installations located in countries that are very different from ours.
So, we have to talk absolutely about collation!
ERROR 468: Cannot resolve the collation conflict between “AAAA” and "BBBB" in the equal to operation
Yes, I am sure! this error will have happened to you at least once:
Message 468, level 16, state 9, row 19
Cannot resolve the collation conflict between “AAAA” and "BBBB" in the equal to operation.
The message say that there was a problem during the two column comparison (equal to).
But what kind of problem?
A problem with the comparison rules!
The comparison rules are defined within the collations.
To better understand let's take an example
Let's create a table with the standard collate of our database and fill it with some data.
Create Table PROVA_TABELLA_COLLATE (
Id int Identity (1,1),
Description Varchar (200) COLLATE Latin1_General_CI_AS NOT NULL)
Insert into dbo.PROVA_TABELLA_COLLATE (Description) values ('Hello')
Now let's create another table setting also a COLLATE for example a collate in use in Greenland where people speak Danish, then let's put some data.
Create Table TRY_TABLE_COLLATE_GREENLAND
(ID int Identity (1,1),
Beskrivelse Varchar (200) COLLATE Danish_Greenlandic_100_CI_AS_KS_WS NOT NULL )
Insert into TEST_TABLE_COLLATE_GREENLAND (Beskrivelse) values ('Hej')
What happens if we perform the select below?
SELECT *
FROM TRY_TABLE_COLLATE US
JOIN TRY_TABLE_COLLATE_GREENLAND THEIR ON US Description = THEIR.Beskrivelse
Error 468 comes out:
Message 468, level 16, state 9, row 19
The conflict between "Danish_Greenlandic_100_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation cannot be resolved in the equal to operation.
Fortunately the T-SQL language allows you to force the collation.
So in order to solve the problem you can force the correct collation.
But how to force a collation?
Well, to force collation is sufficient to add to our Query, after the comparison operations, "COLLATE" followed by the name of one of the two collates.
For example:
SELECT *
FROM TRY_TABLE_COLLATE US
JOIN TRY_TABLE_COLLATE_GREENLAND THEIR ON US Description = THEIR.Beskrivelse COLLATE Danish_Greenlandic_100_CI_AS_KS_WS
Now if you execute the Query it will be run correctly and so ERROR SOLVED!
But..
What is a COLLATE?
The most immediate examples are the ordering of the characters or the fact that our query consider strings as Case sensitive or not.
If someone ask you if SQL Server is case sensitive or not you can simply respond:
It depends! It depends from the collate you are using.
A tips: reading the final part of the name of the collation (eg: Latin1_General_CI_AS) you can understand if:
- _CS our collation is case sensitive
- _CI our collation is case insensitive
- _AS our collation is accent sensitive ('a' is considered equal to 'à')
And so on..
What is your default collate?
In order to see what is the default collate of our system, simply execute the following T-SQL command:
SELECT SERVERPROPERTY ('collation');
That's all for today.
Since i am pretty curious and since many guys who read me are from United States, India, Brazil and so on. I ask you an help!
Please comment this post and tell me which collate are you using in our country!
I will be happy! thank you!
Luca Biondi @ SQLServerPerformance blog!