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.
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 "collate as database_default" 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.
We can’t use Partial Contained databases in the following scenarios:
- Replication, Change Data Capture, Change Tracking
- Cross database queries
Read More from Books Online
SQL Server Books Online is the best place to learn about anything related to SQL Server. So as a starting point, you can read more about contained database here. You can also download the all the scripts used in these two posts from here.