{"id":516,"date":"2011-12-06T01:47:00","date_gmt":"2011-12-06T01:47:00","guid":{"rendered":"\/blogs\/joe\/post\/Contained-DBs-and-collation-conflict.aspx"},"modified":"2013-01-20T10:08:09","modified_gmt":"2013-01-20T18:08:09","slug":"contained-dbs-and-collation-conflict","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/","title":{"rendered":"Contained DBs and collation conflict"},"content":{"rendered":"

The SQL Server 2012 contained database feature has an interesting behavior when it comes to collation considerations between the SQL Server instance default collation and a user database collation. \u00a0<\/span>I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead.<\/span><\/span><\/p>\n

First of all, this demonstration is on SQL Server 11.0.1750 (SQL Server 2012 RC0).\u00a0 <\/span>I\u2019ll start by executing the following in order to determine the default collation of the instance:<\/span><\/span><\/p>\n

SELECT SERVERPROPERTY(‘Collation’)<\/span><\/span><\/p>\n

This returns SQL_Latin1_General_CP1_CI_AS. <\/span><\/span><\/p>\n

Next I\u2019ll create a database that does NOT allow containment, so you can see the pre-2012 behavior:<\/span><\/span><\/p>\n

CREATE DATABASE [PCDBExample_No_CDB]<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>CONTAINMENT = NONE<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>COLLATE French_CS_AI<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

Notice that in addition to designating CONTAINMENT = NONE, I used a collation that was different from the SQL Server instance default.<\/span>\u00a0 <\/span><\/span><\/span><\/p>\n

And next, I\u2019m going to create two tables \u2013 one regular table and one temporary in the newly created database, and then insert identical rows:<\/span><\/span><\/p>\n

USE [PCDBExample_No_CDB]<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE [DemoCollation]<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE #DemoCollation<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

INSERT dbo.DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

INSERT #DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

Now I\u2019ll execute a query that joins the two tables based on the column name:<\/span><\/span><\/p>\n

SELECT p.DemoCollationNM<\/span><\/span><\/p>\n

FROM dbo.DemoCollation p<\/span><\/span><\/p>\n

INNER JOIN #DemoCollation d ON<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>p.DemoCollationNM = d.DemoCollationNM<\/span><\/span><\/p>\n

This returns the following error message:<\/span><\/span><\/p>\n

Msg 468, Level 16, State 9, Line 4<\/span><\/span><\/p>\n

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.<\/span><\/span><\/p>\n

Next I\u2019ll look at sp_help for each table (regular and temporary):<\/span><\/span><\/p>\n

EXEC sp_help [DemoCollation]<\/span><\/span><\/p>\n

USE tempdb<\/span><\/span><\/p>\n

EXEC sp_help #DemoCollation<\/span><\/span><\/p>\n

As our error suggested, the DemoCollation had a collation of French_CS_AI for the DemoCollationNM varchar data type column, but a SQL_Latin1_General_CP1_CI_AS collation for the DemoCollationNM column in the #DemoCollation table.<\/span><\/span><\/p>\n

Now let\u2019s see what happens for a contained database.\u00a0 <\/span>In order to demonstrate a partially contained database, I\u2019ll execute sp_configure as follows:<\/span><\/span><\/p>\n

EXEC sp_configure ‘contained database authentication’, 1<\/span><\/span><\/p>\n

RECONFIGURE<\/span><\/span><\/p>\n

The following code creates a partially contained database and sets up the same test (different database):<\/span><\/span><\/p>\n

CREATE DATABASE [PCDBExample_CDB]<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>CONTAINMENT = PARTIAL<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>COLLATE French_CS_AI<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

USE [PCDBExample_CDB]<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE [DemoCollation]<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE #DemoCollation2<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>(DemoCollationNM varchar(100))<\/span><\/span><\/p>\n

INSERT dbo.DemoCollation<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

INSERT #DemoCollation2<\/span><\/span><\/p>\n

(DemoCollationNM)<\/span><\/span><\/p>\n

VALUES (‘Test Join’)<\/span><\/span><\/p>\n

Now I\u2019ll test the join:<\/span><\/span><\/p>\n

SELECT p.DemoCollationNM<\/span><\/span><\/p>\n

FROM dbo.DemoCollation p<\/span><\/span><\/p>\n

INNER JOIN #DemoCollation2 d ON<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>p.DemoCollationNM = d.DemoCollationNM<\/span><\/span><\/p>\n

This time I get results instead of a collation error:<\/span><\/span><\/p>\n

\"clip_image001\"<\/a><\/span><\/p>\n

If I execute sp_help for #DemoCollation2 in tempdb, I also see that the Collation is French_CS_AI.\u00a0 <\/span>So the containment setting changed the default collation to the user-database\u2019s default instead of the SQL Server instance level default.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

The SQL Server 2012 contained database feature has an interesting behavior when it comes to collation considerations between the SQL Server instance default collation and a user database collation. \u00a0I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead. First of all, this demonstration […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,34],"tags":[],"class_list":["post-516","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-t-sql"],"yoast_head":"\nSQL Server Contained Databases and Collation Issues - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Contained Databases and Collation Issues - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"The SQL Server 2012 contained database feature has an interesting behavior when it comes to collation considerations between the SQL Server instance default collation and a user database collation. \u00a0I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead. First of all, this demonstration […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2011-12-06T01:47:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-20T18:08:09+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/\",\"name\":\"SQL Server Contained Databases and Collation Issues - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2011-12-06T01:47:00+00:00\",\"dateModified\":\"2013-01-20T18:08:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Availability Groups\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/availability-groups\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Contained DBs and collation conflict\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Contained Databases and Collation Issues - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Contained Databases and Collation Issues - Joe Sack","og_description":"The SQL Server 2012 contained database feature has an interesting behavior when it comes to collation considerations between the SQL Server instance default collation and a user database collation. \u00a0I see this new behavior as a benefit, but rather than tell you about it, I\u2019ll step through a demonstration instead. First of all, this demonstration […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/","og_site_name":"Joe Sack","article_published_time":"2011-12-06T01:47:00+00:00","article_modified_time":"2013-01-20T18:08:09+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/","name":"SQL Server Contained Databases and Collation Issues - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2011-12-06T01:47:00+00:00","dateModified":"2013-01-20T18:08:09+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/contained-dbs-and-collation-conflict\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Availability Groups","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/availability-groups\/"},{"@type":"ListItem","position":3,"name":"Contained DBs and collation conflict"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=516"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/516\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}