SQL server 2012 - different database collation
Skrivet den 26 juni 2012 i SQL Server 2012, Level 300, Håkan Winther, sv
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 Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Sommarkollo2012.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sommarkollo2012_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Sommarkollo2012_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