One of the demos from my Contained Databases talk looks at the issues you can have when your database collation does not match your server collation. I’ll walk through the issue here. I’ll show the issue, and then the fix with contained databases.

First, let’s create a database and a table:

-- create db without containment
CREATE DATABASE ucdb2
 COLLATE Japanese_CS_AS
;
go
USE ucdb2
;
go


-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

My server collation is shown here (SQL_Latin1_General_CP1_CI_AS:

Now let’s create the exact same table in tempdb.

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go

If I try to work with these two tables together, I will have problems. As an example, let’s just union query these two items together.

SELECT 
  'udcb2'
,  mychar
 FROM MyTable
UNION ALL
SELECT 
  'tempdb'
, mychar
 FROM #MyTable
;
go

I get an error, as shown here:

The collation error occurs because the query optimizer can’t decide which collation to use. You can easily fix this, as I’ve blogged about before with a collation clause.

However contained databases mean you don’t have to change code. Let’s do the same thing, this time with a contained database.

-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
 COLLATE Japanese_CS_AS
;
go
USE cdb2
;
go

-- Create Unicode Table, add a row
CREATE TABLE MyTable
( mychar NVARCHAR(200)
);
go
INSERT MyTable SELECT 'This is a Japanese Row'
go

-- create temp unicode table
CREATE TABLE #MyTable
( mychar NVARCHAR(200)
);
go
INSERT #MyTable SELECT 'This is a Japanese Row'
go

Now if I run the same statement, I see:

The contained database has correctly resolved the collation issues.

You can check the collations with sp_help with the two table names.

Filed under: Blog Tagged: ContainedDatabases, sql server, syndicated