What's up with the collation of some columns in sys.databases?

I'm attempting to run an UNPIVOT on various columns contained in sys.databases across various versions of SQL Server, ranging from 2005 to 2012.

The UNPIVOT is failing with the following error message:


Msg 8167, Level 16, State 1, Line 48


The type of column "CompatibilityLevel" conflicts with the type of other columns specified in the UNPIVOT list.

The T-SQL:

```
DECLARE @dbname SYSNAME;
SET @dbname = DB_NAME();

SELECT [Database] = unpvt.DatabaseName
, [Configuration Item] = unpvt.OptionName
, [Configuration Value] = unpvt.OptionValue
FROM (
SELECT
DatabaseName = name
, RecoveryModel = CONVERT(VARCHAR(50), d.recovery_model_desc)
, CompatibilityLevel = CONVERT(VARCHAR(50), CASE d.[compatibility_level] WHEN 70 THEN 'SQL Server 7' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' ELSE 'UNKNOWN' END)
, AutoClose = CONVERT(VARCHAR(50), CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, AutoCreateStatistics = CONVERT(VARCHAR(50), CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, AutoShrink = CONVERT(VARCHAR(50), CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, AutoUpdateStatistics = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, AutoUpdateStatisticsAsynch = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, CloseCursorOnCommit = CONVERT(VARCHAR(50), CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
, DefaultCursor = CONVERT(VARCHAR(50), CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
, ANSINULL_Default = CONVERT(VARCHAR(50), CASE

The official word from Microsoft:


Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata (not user metadata) and basically these strings are treated case insensitive (like keywords, so always Latin).


Other columns in system tables that contain user metadata like object names, column names, index names, login names, etc. take the instance or database collation. The columns are collated to proper collation at the time of installation of SQL Server in case of instance collation & at the time of creation of database in case of database collation.

You asked (emphasis mine):


Why is the collation of these columns statically set?

The reason some columns are statically set is so that queries don't need to worry about server or database collation (more importantly: CaSe SenSiTIviTy) to work correctly. This query will always work regardless of collation:

SELECT * FROM sys.databases WHERE state_desc = N'ONLine';

Whereas if server collation were case sensitive, the query above would return 0 rows, just like this does:

SELECT * FROM sys.databases 
  WHERE state_desc COLLATE Albanian_BIN = N'ONLine';

For example, if you install an instance of SQL Server with SQL_Estonian_CP1257_CS_AS collation, then run the following:

SELECT name, collation_name 
FROM master.sys.all_columns
WHERE collation_name IS NOT NULL
AND [object_id] = OBJECT_ID(N'sys.databases');

You will see these results (or something similar, depending on your version of SQL Server):

name                            SQL_Estonian_CP1257_CS_AS
collation_name                  SQL_Estonian_CP1257_CS_AS
user_access_desc                Latin1_General_CI_AS_KS_WS
state_desc                      Latin1_General_CI_AS_KS_WS
snapshot_isolation_state_desc   Latin1_General_CI_AS_KS_WS
recovery_model_desc             Latin1_General_CI_AS_KS_WS
page_verify_option_desc         Latin1_General_CI_AS_KS_WS
log_reuse_wait_desc             Latin1_General_CI_AS_KS_WS
default_language_name           SQL_Estonian_CP1257_CS_AS
default_fulltext_language_name  SQL_Estonian_CP1257_CS_AS
containment_desc                Latin1_General_CI_AS_KS_WS
delayed_durability_desc         SQL_Estonian_CP1257_CS_AS

Now, to demonstrate metadata views that inherit database collation, rather than inheriting server collation from the master database:

CREATE DATABASE server_collation;
GO
CREATE DATABASE albanian COLLATE Albanian_BIN;
GO
CREATE DATABASE hungarian COLLATE Hungarian_Technical_100_CS_AI;
GO

SELECT name, collation_name 
  FROM server_collation.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM albanian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM hungarian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

Results:

server_collation
----------------
name                                 SQL_Estonian_CP1257_CS_AS
collation_name                       SQL_Estonian_CP1257_CS_AS
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  SQL_Estonian_CP1257_CS_AS

albanian
----------------
name                                 Albanian_BIN
collation_name                       Albanian_BIN
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Albanian_BIN

hungarian
----------------
name                                 Hungarian_Technical_100_CS_AI
collation_name                       Hungarian_Technical_100_CS_AI
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Hungarian_Technical_100_CS_AI

So you can see that in this case several columns inherit the database collation, while others are fixed to this "generic" Latin1 collation, meaning it is used to insulate certain names and properties from case sensitivity issues as described above.

If you try to perform a UNION, for example:

SELECT name FROM albanian.sys.columns
UNION ALL
SELECT name FROM server_collation.sys.columns;

You get this error:


Msg 451, Level 16, State 1

Cannot resolve collation conflict between "Albanian_BIN" and "SQL_Estonian_CP1257_CS_AS" in UNION ALL operator occurring in SELECT statement column 1.

Similarly, if you

StackExchange Database Administrators Q#118158, answer score: 17