(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 115-------------------------------------------------- Contained Databases By: srinivasma_exceldbp Date: December 11, 2014, 5:08 am --------------------------------------------------------- -- to enable contained database on the instance sp_configure 'show advanced options', 1 reconfigure go sp_configure 'contained database authentication', 1 reconfigure go sp_configure 'show advanced options', 0 reconfigure go -- show current value of user options, must be zero to enable contained databases sp_configure 'user options' go -- 1. New DDL CREATE DATABASE test_contained CONTAINMENT = PARTIAL USE test_contained GO -- 2. user with authentication CREATE USER bob WITH PASSWORD = 'StrongPW1' GO -- 3. might cross an application boundary select * from sys.dm_db_uncontained_entities GO -- create an uncontained entity CREATE PROC P1 AS SELECT * FROM sys.endpoints ; GO SELECT SO.name, UE.* FROM sys.dm_db_uncontained_entities AS UE LEFT JOIN sys.objects AS SO ON UE.major_id = SO.object_id; GO -- there's also an XEvent for uncontained entities, for testing user code select * from sys.dm_db_uncontained_entities GO -- 4. Modified functionality ALTER DATABASE CURRENT -- .... GO /* Not allowed in partially contained DB Uncontained tables, e.g. a table that uses an uncontained function in a constraint or computed column Check constraints that directly use uncontained entities or functions. Numbered procedures Computed columns that directly use uncontained entities or functions. Schema-bound objects that depend on built-in functions with collation changes Binding change resulting from collation changes, including references to objects, columns, symbols, or types. */ -- this seems to work, but not in a contained DB CREATE PROCEDURE [dbo].[foo];1 @x int AS PRINT 'x is ' + CONVERT(varchar(8), @x) GO CREATE PROCEDURE [dbo].[foo];2 @x int AS PRINT 'x is ' + CONVERT(varchar(8), @x) GO select * from sys.dm_db_uncontained_entities GO -- 5. Handles collations differently --In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS. --This collation is the same for all contained databases on all instances of SQL Server and cannot be changed. SELECT name, collation_name, * FROM sys.databases -- collations supported SELECT * from fn_helpcollations() CREATE TABLE #T1 ( id int, col1 varchar(20) ) -- collation not same as tempdb_collation SELECT collation_name ,(select collation_name from sys.databases where name = 'tempdb') as tempdb_collation ,* from tempdb.sys.tables t JOIN tempdb.sys.columns c ON c.object_id = t.object_id WHERE collation_name IS NOT NULL GO -- this is similar to (but automatic) CREATE TABLE #T2 (T2_txt nvarchar(max) COLLATE DATABASE_DEFAULT); GO -- 5b. New concept in contained database, catalog_default -- catalog_default is used for variables, metadata, tempdb_metadata, goto labels, cursor names -- in a contained database, catalog_default can't be changed -- in a non-contained database, CATALOG_DEFAULT will return the current database collation -- but I can change the database collation of a contained database -- Use of new CURRENT keyword for ALTER DATABASE ALTER DATABASE CURRENT COLLATE Turkish_CS_AS GO CREATE TABLE T1 (T1_txt nvarchar(max) COLLATE CATALOG_DEFAULT); GO CREATE TABLE T2(T2_txt nvarchar(max)); GO SELECT collation_name ,* from sys.tables t JOIN sys.columns c ON c.object_id = t.object_id WHERE collation_name IS NOT NULL GO CREATE TABLE #T3( id int, col1 varchar(20) ) -- collation not same as tempdb_collation -- database default (not catalog default) used for temp tables SELECT collation_name ,(select collation_name from sys.databases where name = 'tempdb') as tempdb_collation ,* from tempdb.sys.tables t JOIN tempdb.sys.columns c ON c.object_id = t.object_id WHERE collation_name IS NOT NULL GO -- metadata uses CATALOG_COLLATION CREATE TABLE fooI (id int) CREATE TABLE FOOİ (id int) CREATE TABLE FOOi (id int) -- this table not created GO CREATE DATABASE foo COLLATE Turkish_CS_AS USE foo go -- table names still case-sensitive, Turkish I not same as capital I or little i -- all tables created CREATE TABLE fooI (id int) CREATE TABLE FOOİ (id int) CREATE TABLE FOOi (id int) -- Setting partial containment on doesn't work ALTER DATABASE foo SET CONTAINMENT=PARTIAL USE master DROP DATABASE foo /* -- catalog_default is used for variables, metadata, tempdb_metadata, goto labels, cursor names -- in a contained database, it can't be changed -- but I can change the database collation of a contained database CREATE FUNCTION dbo.f(@x INT) RETURNS INT AS BEGIN DECLARE @I INT = 1 DECLARE @İ INT = 2 RETURN @x * @i END GO -- In case-sensitive Latin *instance* collation, produces an error -- In case-insenitive Turkish *instance* collation, using DATABASE_COLLATION returns 8 -- In case-insenitive Latin *instance* collation, returns 4 SELECT dbo.f(4); */ *****************************************************