First published on MSDN on Feb 26, 2013
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:
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:
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.
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