If you have databases with another default collation than the instance default, you may get something like the following error:
[red_box]Msg 468, Level 16, State 9, Line 30 Cannot resolve the collation conflict between “Finnish_Swedish_CI_AS” and “Finnish_Swedish_100_CI_AS” in the equal to operation.[/red_box]
The collation determines the character sort ordering and equality of string comparisons in a database. There is a collation setting on the instance level that determines the collation in system databases. There is also a setting at the database level, and if it is not explicitly set, it is the same as the instance collation setting. Further, it is possible to set the collation on each column individually.
In the following example, I am using Finnish_Swedish_CI_AS as the instance default. I then create a database with the collation Finnish_Swedish_100_CI_AS, to make it different from the instance default.
When I create an object in tempdb, the collation becomes the instance default (Finnish_Swedish_CI_AS). The differing collations causes string comparisons to generate the Msg 468 error.
CREATE DATABASE CollationTestDb COLLATE Finnish_Swedish_100_CI_AS
GO
USE CollationTestDb
GO
CREATE TABLE dbo.ColTest
(
COLTEXT VARCHAR(100) NOT NULL
)
GO
INSERT dbo.ColTest VALUES ('Tomas Lind')
GO
CREATE TABLE #TMP_TABLE
(
COLTEXT VARCHAR(100) NOT NULL
)
GO
INSERT #TMP_TABLE VALUES ('Tomas Lind')
GO
SELECT * FROM dbo.ColTest T0 JOIN #TMP_TABLE T1 ON T0.COLTEXT = T1.COLTEXT
One way to avoid the error is to use the “DATABASE_DEFAULT” collation when creating the object in tempdb. The create statement would then be:
CREATE TABLE #TMP_TABLE ( COLTEXT VARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL ) GO
Or, you can specify the collation directly:
CREATE TABLE #TMP_TABLE ( COLTEXT VARCHAR(100) COLLATE Finnish_Swedish_100_CI_AS NOT NULL ) GO
To change the default in a database is easy, this can be done with the “ALTER DATABASE” statement:
ALTER DATABASE CollationTestDb COLLATE Finnish_Swedish_CI_AS GO
But note that this does not change the collation on already existing objects in the database. Each table and column must be changed individually. For a small database with few columns to change, it can be done by going through the string columns and changing the column property in the table designer:
And then clicking the “Restore Default” button (the database default must first be set to the collation you want):
Or, if you prefer to use T-SQL:
ALTER TABLE dbo.ColTest ALTER COLUMN COLTEXT VARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL GO
In SQL Server 2012 the concept of contained databases was introduced. One feature of contained databases is that the handling of collation has changed for common objects in the instance. Objects in tempdb for instance, use the database default instead of the instance default. Read more here.
