SQL server 2012 – different database collation

A common problem with SQL server consolidation is different default collations between databases and the server default collation. The problem occurs with tempdb that will get the server default collation and when you create a temporary table that you use to compare or join to another database table, you’ll get an exception. In SQL server 2012 you can create a partial contained database, and by using contained databases you’ll inherit the database default collation when creating the temporary table. This will solved the issue with the exception.

This new feature will save you money, as you can put databases with different collations on the same instance and reduce the number of instances needed. Try the following script:

USE MASTER;

GO 

CREATE DATABASE [Sommarkollo2012] CONTAINMENT = PARTIAL ON  PRIMARY
( NAME = N’Sommarkollo2012′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATASommarkollo2012.mdf’ , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Sommarkollo2012_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATASommarkollo2012_log.ldf’ , SIZE = 2048KB , FILEGROWTH = 10%)
COLLATE Finnish_Swedish_CS_AI
GO 

SELECT
            serverCollation=SERVERPROPERTY(‘collation’),
            sommarKollorCollation = DATABASEPROPERTYEX(‘SommarKollo2012′,’collation’),
            tempdbCollation=DATABASEPROPERTYEX(‘tempdb’,’collation’); 

USE SommarKollo2012;
GO 

CREATE TABLE CollationTest
(
            I INT NOT NULL IDENTITY(1,1),
            A VARCHAR(50) NOT NULL
);

 CREATE TABLE #CollationTest
(
            I INT NOT NULL IDENTITY(1,1),
            A VARCHAR(50) NOT NULL
); 

INSERT INTO CollationTest (A) VALUES(‘Håkan Winther’);
INSERT INTO #CollationTest (A) VALUES(‘Håkan Winther’);

SELECT A FROM CollationTest
INTERSECT
SELECT A FROM #CollationTest;

GO 

BEGIN TRY
            DROP TABLE #CollationTest;
END TRY
BEGIN CATCH
END CATCH
GO 

USE master;
GO 

DROP DATABASE [Sommarkollo2012];
GO 

If you change the containment to none, the exception will occur.

If you have any questions regarding SQL server 2012 and collations, feel free to call us and one of our SQL server experts will help you.

/Håkan Winther