DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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);
       */
       *****************************************************