Contained Database 102

In my previous post, we discussed about Contained database, how we configure the SQL Server instance, how to create a Contained database, contained user creation along with how to connect using Contained user account. In this post, we are going to look at how contained database removes the dependency on collation setting.

Collation Setting

According to Books Online, a Collation encodes the rules governing the proper use of characters for either a language or an alphabet.

A SQL Server collation defines how database engine stores and operates on characters and Unicode data.

Not only Collation setting apply to the stored data; but it also to variables names, metadata and temporary objects.

As of Today

As of today, while moving a database from one server to other, we normally verify that the collation setting of the database, source server & destination server are same. Most of time, we use the server collation setting, as default, for the database; however, the problem starts when we create a database with different collation setting or moved the database to an instance with different collation setting. Look at the following script block, which will create a database with collation setting Turkish_CI_AS, on a SQL Server instance with collation setting of SQL_Latin1_General_CP1_CI_AS.

-- Create a database with collation setting Turkish_CI_AS
use [master];
go
if exists (Select 1 from sys.databases where [name] = 'NonContainedDB')
	begin
		alter database [NonContainedDB]
			set single_user with rollback immediate;
		drop database [NonContainedDB];
	end
go
create database [NonContainedDB]
	collate Turkish_CI_AS;
go

Next we are going to create a table and a temp table in the above database.

-- create a table in the NonContainedDB
use [NonContainedDB];
go
create table tbltest (
testname	char(10)
);
go
-- create a temp table
use [NonContainedDB];
go
create table #tbltest (
testname	char(10)
);
go
-- join the above two tables
use [NonContainedDB];
go
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testname;
go

Joining these two tables will throw the following error.

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Turkish_CI_AS" in the equal to operation.

We can fix this issue, by using the COLLATE DATABASE_DEFAULT clause, while defining the temporary objects. If we miss it anytime, then we are back to the same problem.

Collation Setting @ Contained database

Partial contained database, introduced the concept of Catalog Collation, which will be used for system metadata and temporary objects. The new Catalog collation will have a collation setting Latin1_General_100_CI_AS_WS_KS_SC and will be used for metadata (Variables, Cursors, temp table, table name, and GoTo level). The catalog collation setting is fixed for all contained databases across all SQL Server 2012 instances.

While the catalog collation affects the metadata, the database collation will be used to store the data inside the tables & temp tables.

Continuing with the above example, if we enable the containment setting of the above database and re-run the join query, our query will complete without any error. See the script below

-- Change the Containment option to Partial
use [master];
go
alter database [NonContainedDB]
	set containment = Partial;
go
-- create a temp table, without &quot;collate as database_default&quot;
use [NonContainedDB];
go
create table #tbltest (
testchar	char(10)
);
go
-- Perform the same join which failed earlier
use [NonContainedDB];
go
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testchar;
go

Benefits with Partial Containment

As a standard procedure, while moving a database from one instance to another, I always check the collation setting of the user database, source instance, and destination instance. Apart from collation setting, we also need to transfer the logins to the other instance, so that the application can connect. Every database should have at least these two dependencies. With Partial containment, these dependencies are removed.

As a databases developer, we don’t have to worry about the instance collation anymore. With Partial Contained database, now we can develop in our own environment and our database can run on a SQL Server 2012 instance, without any issue. Partial Contained database can also be used with the new high availability feature, AlwaysOn, a feature introduced with SQL Server 2012.

Concerns

We can’t use Partial Contained databases in the following scenarios:

Replication, Change Data Capture, Change Tracking
Cross database queries