Today's question (by Steve Jones - SSC Editor):

The Partial Computed Column

ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                    END;
GO

Think you know the answer? Click here, and find out if you are right.

Database containment and c

Lets start with a simple, new database with Latin1_General_CI_AS_KS as collation:

USE [master]
go
CREATE DATABASE [test_cont]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'test_cont', FILENAME = N'c:temptest_cont.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON 
( NAME = N'test_cont_log', FILENAME = N'c:temptest_cont_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [test_cont] COLLATE Latin1_General_CI_AS_KS

When we run the following query, we see, that the collation of the name column in the view sys.objects is Latin1_General_CI_AS_KS (as expected) and Latin1_General_CI_AS_KS_WS for type / type_desc.

USE test_cont
GO
SELECT c.name, c.collation_name
FROM sys.all_objects AS ao
INNER JOIN sys.all_columns AS c
ON c.object_id = ao.object_id -- will be -385 per default for sys.objects
WHERE ao.name = 'objects'
AND ao.schema_id = SCHEMA_ID('sys')
AND c.collation_name IS NOT NULL;
GO

Now we change the database to partially containment (allowing us to create user, that exists only in this database but not as login on the server level):

-- don't do it on production without exactly knowing, what you do!!!
EXEC sys.sp_configure @configname = 'contained database authentication', @configvalue = 1; 
RECONFIGURE

ALTER DATABASE [test_cont] SET CONTAINMENT = NONE WITH NO_WAIT

Question:

What happens, when we execute this code?

USE test_cont
GO
SELECT c.name, c.collation_name
FROM sys.all_objects AS ao
INNER JOIN sys.all_columns AS c
ON c.object_id = ao.object_id -- will be -385 per default for sys.objects
WHERE ao.name = 'objects'
AND ao.schema_id = SCHEMA_ID('sys')
AND c.collation_name IS NOT NULL;
GO

PS: tested on SQL 2016 SP2 Enterprise

Answer: sys.objects.name has now the collation Latin1_General_100_CI_AS_KS_WS_SC

Explanation: I couldn't find a official documentation regarding this behavior, but putting a database in the partially containment mode changes the collation of several system tables (sys.schemas is another one). Changing back the containment to NONE again "restores" the default database collation. We stumbled over this, when one of our devs created a UNION ALL query over sys.objects in different databases including one partially contained database (caused an error, since the collations in the different SELECTs was not compatible). For more informations regarding Containment, you can read this article.  

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.