Systems Engineering and RDBMS

Cannot Resolve Collation Conflict in the equal to operation

Posted by decipherinfosys on December 16, 2007

Collation in SQL Server are used to specify how the string data-types are sorted and compared with each other. This is based on the languages and their locales.  There are Windows collations as well as SQL Server collations and we will cover collations in detail in a future blog post series that we intend to do on localization and globalization.  In this post, let’s talk about what are the different levels at which collations can get applied in SQL Server and then when can you receive the error that it is title of this post and how to get around it.

Collations can apply at 4 levels in SQL Server:

1. Instance Level Collations:  This is specified at the time of the installation.  Whatever collation is selected also becomes the collation for the 4 system databases: Master, Model, MSDB and TempDB.  If youa re not sure what instance level collation you have in place, you can check it by running this command:

SELECT SERVERPROPERTY(‘collation’)

On my instance, I get: SQL_Latin1_General_CP1_CI_AS

If you want to see all the different collations that you available, you can query this built-in function:

SELECT * from ::fn_helpcollations()

One thing to remember is that once an instance has been created, you cannot change it’s collation.  If you wish to do so, you will have to un-install and re-install the instance again.

2.  Database Level Collations: When creating a database, if you do not specify any collation, it defaults to the collation that is used by the model database which practically means that it defaults to the instance collation.  You can retrieve the collation for your database by using this command:

SELECT DATABASEPROPERTYEX(‘DECIPHER_TEST’,’collation’)

On my instance, since I had not specified any collation, it defaulted to the same collation as the instance:

SQL_Latin1_General_CP1_CI_AS

However, I can change the collation for the database by using the “ALTER DATABASE” command in SQL Server 2005:

ALTER DATABASE DECIPHER_TEST COLLATE Chinese_PRC_BIN;

Changing the database collation does not change the collation of the already created objects (tables, columns).

3.  Column Level collations: When we create tables, we can specify collations for each string type column using the COLLATE clause.  If we omit it, then it defaults to the collation of the database in which the table is being created.  Just like the database collation, the collation of the table column(s) can also be changed later on.  Here is an example:

USE DECIPHER_TEST
GO
CREATE TABLE COLL_TEST (COL1 NVARCHAR(10));
GO

ALTER TABLE COLL_TEST ALTER COLUMN COL1 NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS;

4.  Expression level collations: One can even specify collations at the expression level.  This gets applied at run time and can be used in places where there is a comparison of columns with different collations or if we want to order the data by a certain collation.  Example:

SELECT * FROM COLL_TEST ORDER BY COL1 COLLATE Chinese_PRC_BIN;

So, in what situations can one get the error that is the title of this blog post?  i.e. the error:

“Cannot Resolve Collation Conflict in the equal to operation”

Let us try to simulate this by creating another table which has a column with a different collation:

CREATE TABLE COLL_TEST (COL1 NVARCHAR(10));
ALTER TABLE COLL_TEST ALTER COLUMN COL1 NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS;

CREATE TABLE COLL_TEST_2 (COL2 NVARCHAR(10));
ALTER TABLE COLL_TEST_2 ALTER COLUMN COL2 NVARCHAR(10) COLLATE Chinese_PRC_BIN;

INSERT INTO COLL_TEST VALUES (‘Chloe’);
INSERT INTO COLL_TEST VALUES (‘Colt’);
INSERT INTO COLL_TEST VALUES (‘Count’);

INSERT INTO COLL_TEST_2 VALUES (‘Chloe’);
INSERT INTO COLL_TEST_2 VALUES (‘Colt’);
INSERT INTO COLL_TEST_2 VALUES (‘Count’);

SELECT *
FROM COLL_TEST AS A
INNER JOIN COLL_TEST_2 AS B
ON A.COL1 = B.COL2

This will give the error:

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between “Chinese_PRC_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Since the column was created with a specific collation, we would assume that there was a valid business reason to do so and that changing the column collation is not an option.  So, we are left at the level 4 option – i.e. changing the collation at run-time at the expression level.  We can do so quite easily:

SELECT *
FROM COLL_TEST AS A
INNER JOIN COLL_TEST_2 AS B
ON A.COL1 COLLATE DATABASE_DEFAULT = B.COL2 COLLATE DATABASE_DEFAULT

In this particular example, since we know that both the tables are in the same database, we can use the database collation to sort out this issue.  However, the three more likely scenarios in which you will be facing these issues would be:

a) When you have 2 or more databases on the same instance with different collations and you need to write queries that span these databases.  Maybe one is a database that is used for the North America Sales and one is a database that you use for all of your Asian operations.  In such a scenario, if you are going to be joining on a string data-type column, you can then change the collation at the expression level after deciding which collation you want to use.

b) When you have distributed queries and you are joining tables in a database on one instance with tables in the database on another instance.  The same resolution as mentioned above applies in that case as well.

c) When you have a database (or a table in the database) that has a different collation than the instance collation and you have tables in the database joining with temporary tables.  Since temporary tables get created in tempdb, they will inherit from the instance and will have a collation that is different than the database/column level collation.  Resolution still remains the same as was mentioned in #a.  It has to be changed at the expression level in order to circumvent the issue.  Another resolution could be to create temp table with the collation of the database/column.

So, as you can glean from above, making a decision on which collation to use and where is very important at design time.  The instance level or database level or column level collation choices are all design time choices.  The expression level changes to collation is to address the mis-matches that can occur later on as you try to integrate different application databases from different parts of the world or with different collation requirements.  In today’s day and age where localization and globalization is becoming more and more important, it is important to become aware of the collation options as well as UNICODE options.

To that effect, we will soon be starting a blog series on it for Oracle as well as SQL Server and will share our experiences when working with applications with databases with different character sets needs, different collation needs and different translation needs.  You can search this site for UNICODE and/or UTF8 to get more information and we will be adding more on AL32UTF8, UTF8, UCS-2 and the different mappings and encodings.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: