In my last post, I went over partial database containment and contained users.  This post will focus on the other piece of functionality in partial containment… collation.  The collation of data determines how data is sorted and compared.  When all databases are using the same collation as the instance collation (selected during install), then there is really no need for containment of the collation.  The problem arises when you are hosting multiple application databases on your instance that require a separate collation.  The most typical example of this is when objects are created in tempdb.  They will have the instance default collation.  If the collations between the two are different then you may see an error similar to this one below:

Msg 468, Level 16, State 9, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

In many cases, this can be resolved easily by using COLLATE to define the collation of the column to prevent this issue.  But what happens when you have a vendor application that will not allow you to change their code?  This is where having a contained collation will come into play. 

This problem was resolved by creating the catalog collation.  This collation is for system metadata and transient objects.  This means that any temporary metadata, variables, etc. will use the catalog default collation and not the database collation. The catalog collation is Latin1_General_100_CI_AS_WS_KS_SC,   and it is the same for all contained databases in the instance.  This does NOT mean that all contained databases on an instance must have the same collation,this is just the catalog collation that is predefined. The following chart will help illustrate the difference:

clip_image001

Many of us don’t deal with collation issues on a day to day basis, so I am including some scripts that will help you explore contained collation a little more.  I included the entire script in one piece at the bottom of the post for ease of copy/paste.

Run this first section to enable containment and create the databases (note that if you still have the 3 databases created from part 1 then you can skip over that part here):

--enable contained dbs

EXEC sys.sp_configure N'contained database authentication', 1

GO

RECONFIGURE

GO

 

--Create the 3 dbs, all have different collation from the instance collation

CREATE DATABASE [MyDB]--not contained

 COLLATE Latin1_General_CI_AS_KS_WS

 GO

CREATE DATABASE [MyContainedDB] --partially contained

 CONTAINMENT = PARTIAL

 COLLATE Latin1_General_CI_AS_KS_WS

 GO

 CREATE DATABASE [MyContainedDBToo] --partially contained to illustrate multiple collations

 CONTAINMENT = PARTIAL

 COLLATE Latin1_General_CS_AS_KS

 GO

 

In the first database, there is no containment.

USE MyDB

GO

--Create Static Table

CREATE TABLE MyTable

      (mycolumn1 nvarchar,

      mycolumn2 nvarchar);

GO

 

--Show column and server collation difference

SELECT name, collation_name

FROM sys.columns

WHERE name LIKE 'mycolumn%' ;

GO

 

select SERVERPROPERTY ('collation')

From the 2 above statements, you should see the following result set to show you that the columns in the table have a different collation than the instance:

clip_image003

 

Then create a temp table and join to it from MyTable, you will get a collation conflict.

CREATE TABLE #MyTempTable   

(mycolumn1 nvarchar,

      mycolumn2 nvarchar) ;

GO

SELECT T1.mycolumn1, T2.mycolumn1

FROM MyTable T1

JOIN #MyTempTable T2

    ON T1.mycolumn1 = T2.mycolumn1

Msg 468, Level 16, State 9, Line 10

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.

Now drop the temp table and rerun the select with a collation conversion of the columns.  This works... until you find out that this is a vendor application and the mention the dreaded “unsupported” word.

--drop temp table

DROP TABLE #MyTempTable

--Now define the temp table with the db collation

--there will be no error when we select

CREATE TABLE #MyTempTable   

(mycolumn1 nvarchar COLLATE Latin1_General_CI_AS_KS_WS ,

      mycolumn2 nvarchar  COLLATE Latin1_General_CI_AS_KS_WS) ;

 

SELECT T1.mycolumn1, T2.mycolumn1

FROM MyTable T1

JOIN #MyTempTable T2

    ON T1.mycolumn1 = T2.mycolumn1

 

Database containment to the rescue! Here you will switch to a contained database.

--switch dbs

USE MyContainedDB

GO

 

--Create static table

CREATE TABLE MyTable

      (mycolumn1 nvarchar,

      mycolumn2 nvarchar);

GO

 

--Show column and server collation difference

SELECT name, collation_name

FROM sys.columns

WHERE name LIKE 'mycolumn%' ;

GO

 

select SERVERPROPERTY ('collation')

Note here that your result set looks identical to the non-contained db.  That is OK.  Remember that contained databases use the catalog collation for temporary metadata.

 

clip_image003

 

Here is where we had an error before, but this time it will work!

--drop temp table

DROP TABLE #MyTempTable

--Create the temp table

--Temp objects use the CATALOG COLLATION for tempdb

--since this db is partially contained

 

CREATE TABLE #MyTempTable   

(mycolumn1 nvarchar,

      mycolumn2 nvarchar) ;

GO

SELECT T1.mycolumn1, T2.mycolumn1

FROM MyTable T1

JOIN #MyTempTable T2

    ON T1.mycolumn1 = T2.mycolumn1

I also included a separate database with a third collation just to show that you can have a number of databases with a number of collations.

--switch dbs

USE MyContainedDBToo

GO

--Create static table

CREATE TABLE MyTable

      (mycolumn1 nvarchar,

      mycolumn2 nvarchar);

GO

--Show column and server collation difference

SELECT name, collation_name

FROM sys.columns

WHERE name LIKE 'mycolumn%' ;

GO

select SERVERPROPERTY ('collation')

--drop temp table

DROP TABLE #MyTempTable

 

--Create the temp table

--Temp objects use the CATALOG COLLATION for tempdb

--since this db is partially contained

CREATE TABLE #MyTempTable   

(mycolumn1 nvarchar,

      mycolumn2 nvarchar) ;

GO

SELECT T1.mycolumn1, T2.mycolumn1

FROM MyTable T1

JOIN #MyTempTable T2

    ON T1.mycolumn1 = T2.mycolumn1

 

Now you have an understanding of what contained databases can do.  Perhaps you have some candidates in your environment for database containment.

Lisa Gardner – SQL Premier Field Engineer