Contained DBs and collation conflict

The SQL Server 2012 contained database feature has an interesting behavior when it comes to collation considerations between the SQL Server instance default collation and a user database collation.  I see this new behavior as a benefit, but rather than tell you about it, I’ll step through a demonstration instead.

First of all, this demonstration is on SQL Server 11.0.1750 (SQL Server 2012 RC0).  I’ll start by executing the following in order to determine the default collation of the instance:

SELECT SERVERPROPERTY(‘Collation’)

This returns SQL_Latin1_General_CP1_CI_AS.

Next I’ll create a database that does NOT allow containment, so you can see the pre-2012 behavior:

CREATE DATABASE [PCDBExample_No_CDB]

 CONTAINMENT = NONE

 COLLATE French_CS_AI

GO

Notice that in addition to designating CONTAINMENT = NONE, I used a collation that was different from the SQL Server instance default. 

And next, I’m going to create two tables – one regular table and one temporary in the newly created database, and then insert identical rows:

USE [PCDBExample_No_CDB]

GO

CREATE TABLE [DemoCollation]

               (DemoCollationNM varchar(100))

GO

CREATE TABLE #DemoCollation

               (DemoCollationNM varchar(100))

INSERT dbo.DemoCollation

(DemoCollationNM)

VALUES (‘Test Join’)

INSERT #DemoCollation

(DemoCollationNM)

VALUES (‘Test Join’)

Now I’ll execute a query that joins the two tables based on the column name:

SELECT p.DemoCollationNM

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

               p.DemoCollationNM = d.DemoCollationNM

This returns the following error message:

Msg 468, Level 16, State 9, Line 4

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.

Next I’ll look at sp_help for each table (regular and temporary):

EXEC sp_help [DemoCollation]

USE tempdb

EXEC sp_help #DemoCollation

As our error suggested, the DemoCollation had a collation of French_CS_AI for the DemoCollationNM varchar data type column, but a SQL_Latin1_General_CP1_CI_AS collation for the DemoCollationNM column in the #DemoCollation table.

Now let’s see what happens for a contained database.  In order to demonstrate a partially contained database, I’ll execute sp_configure as follows:

EXEC sp_configure ‘contained database authentication’, 1

RECONFIGURE

The following code creates a partially contained database and sets up the same test (different database):

CREATE DATABASE [PCDBExample_CDB]

 CONTAINMENT = PARTIAL

 COLLATE French_CS_AI

GO

USE [PCDBExample_CDB]

GO

CREATE TABLE [DemoCollation]

               (DemoCollationNM varchar(100))

GO

CREATE TABLE #DemoCollation2

               (DemoCollationNM varchar(100))

INSERT dbo.DemoCollation

(DemoCollationNM)

VALUES (‘Test Join’)

INSERT #DemoCollation2

(DemoCollationNM)

VALUES (‘Test Join’)

Now I’ll test the join:

SELECT p.DemoCollationNM

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation2 d ON

               p.DemoCollationNM = d.DemoCollationNM

This time I get results instead of a collation error:

If I execute sp_help for #DemoCollation2 in tempdb, I also see that the Collation is French_CS_AI.  So the containment setting changed the default collation to the user-database’s default instead of the SQL Server instance level default.