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. \u00a0<\/span>I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead.<\/span><\/span><\/p>\n

First of all, this demonstration is on SQL Server 11.0.1750 (SQL Server 2012 RC0).\u00a0 <\/span>I\u2019ll start by executing the following in order to determine the default collation of the instance:<\/span><\/span><\/p>\n

SELECT SERVERPROPERTY(‘Collation’)<\/span><\/span><\/p>\n

This returns SQL_Latin1_General_CP1_CI_AS. <\/span><\/span><\/p>\n

Next I\u2019ll create a database that does NOT allow containment, so you can see the pre-2012 behavior:<\/span><\/span><\/p>\n

CREATE DATABASE [PCDBExample_No_CDB]<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>CONTAINMENT = NONE<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>COLLATE French_CS_AI<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

Notice that in addition to designating CONTAINMENT = NONE, I used a collation that was different from the SQL Server instance default.<\/span>\u00a0 <\/span><\/span><\/span><\/p>\n

And next, I\u2019m going to create two tables \u2013 one regular table and one temporary in the newly created database, and then insert identical rows:<\/span><\/span><\/p>\n

USE [PCDBExample_No_CDB]<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE [DemoCollation]<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE #DemoCollation<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

INSERT dbo.DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

INSERT #DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

Now I\u2019ll execute a query that joins the two tables based on the column name:<\/span><\/span><\/p>\n

SELECT p.DemoCollationNM<\/span><\/span><\/p>\n

FROM dbo.DemoCollation p<\/span><\/span><\/p>\n

INNER JOIN #DemoCollation d ON<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>p.DemoCollationNM = d.DemoCollationNM<\/span><\/span><\/p>\n

This returns the following error message:<\/span><\/span><\/p>\n

Msg 468, Level 16, State 9, Line 4<\/span><\/span><\/p>\n

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.<\/span><\/span><\/p>\n

Next I\u2019ll look at sp_help for each table (regular and temporary):<\/span><\/span><\/p>\n

EXEC sp_help [DemoCollation]<\/span><\/span><\/p>\n

USE tempdb<\/span><\/span><\/p>\n

EXEC sp_help #DemoCollation<\/span><\/span><\/p>\n

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.<\/span><\/span><\/p>\n

Now let\u2019s see what happens for a contained database.\u00a0 <\/span>In order to demonstrate a partially contained database, I\u2019ll execute sp_configure as follows:<\/span><\/span><\/p>\n

EXEC sp_configure ‘contained database authentication’, 1<\/span><\/span><\/p>\n

RECONFIGURE<\/span><\/span><\/p>\n

The following code creates a partially contained database and sets up the same test (different database):<\/span><\/span><\/p>\n

CREATE DATABASE [PCDBExample_CDB]<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>CONTAINMENT = PARTIAL<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>COLLATE French_CS_AI<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

USE [PCDBExample_CDB]<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE [DemoCollation]<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE #DemoCollation2<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

INSERT dbo.DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

INSERT #DemoCollation2<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

Now I\u2019ll test the join:<\/span><\/span><\/p>\n

SELECT p.DemoCollationNM<\/span><\/span><\/p>\n

FROM dbo.DemoCollation p<\/span><\/span><\/p>\n

INNER JOIN #DemoCollation2 d ON<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>p.DemoCollationNM = d.DemoCollationNM<\/span><\/span><\/p>\n

This time I get results instead of a collation error:<\/span><\/span><\/p>\n

<\/a><\/span><\/p>\n

If I execute sp_help for #DemoCollation2 in tempdb, I also see that the Collation is French_CS_AI.\u00a0 <\/span>So the containment setting changed the default collation to the user-database\u2019s default instead of the SQL Server instance level default.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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. \u00a0I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead. First of all, this demonstration […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,34],"tags":[],"class_list":["post-516","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-t-sql"],"yoast_head":"\n