21

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 d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  )
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc)
        , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

This is designed to provide a nicely formatted list of database options for the given database, similar to:

+----------+----------------------------+----------------------------+
| Database | Configuration Item         | Value in Use               |
+----------+----------------------------+----------------------------+
| master   | RecoveryModel              | SIMPLE                     |
| master   | CompatibilityLevel         | SQL Server 2008            |
| master   | AutoClose                  | FALSE                      |
| master   | AutoCreateStatistics       | TRUE                       |
| master   | AutoShrink                 | FALSE                      |
| master   | AutoUpdateStatistics       | TRUE                       |
| master   | AutoUpdateStatisticsAsynch | FALSE                      |
| master   | CloseCursorOnCommit        | FALSE                      |
| master   | DefaultCursor              | GLOBAL                     |
| master   | ANSINULL_Default           | FALSE                      |
| master   | ANSINULLS_Enabled          | FALSE                      |
| master   | ANSIPadding_Enabled        | FALSE                      |
| master   | ANSIWarnings_Enabled       | FALSE                      |
| master   | ArithmeticAbort_Enabled    | FALSE                      |
| master   | ConcatNullYieldsNull       | FALSE                      |
| master   | CrossDBOwnerChain          | TRUE                       |
| master   | DateCorrelationOptimized   | FALSE                      |
| master   | NumericRoundAbort          | FALSE                      |
| master   | Parameterization           | SIMPLE                     |
| master   | QuotedIdentifiers_Enabled  | FALSE                      |
| master   | RecursiveTriggers_Enabled  | FALSE                      |
| master   | TrustWorthy                | TRUE                       |
| master   | VARDECIMAL_Storage         | TRUE                       |
| master   | PageVerify                 | CHECKSUM                   |
| master   | BrokerEnabled              | FALSE                      |
| master   | DatabaseReadOnly           | FALSE                      |
| master   | EncryptionEnabled          | FALSE                      |
| master   | RestrictedAccess           | MULTI_USER                 |
| master   | Collation                  | Latin1_General_CI_AS_KS_WS |
+----------+----------------------------+----------------------------+

When I run this in a server with Latin1_General_CI_AS_KS_WS collation, the statement succeeds. If I modify the T-SQL so that certain fields have a COLLATE clause, it will run on servers that have other collations.

The code that works on servers with collations other than Latin1_General_CI_AS_KS_WS is:

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) COLLATE SQL_Latin1_General_CP1_CI_AS
        , 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 d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  ) COLLATE SQL_Latin1_General_CP1_CI_AS
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc) COLLATE SQL_Latin1_General_CP1_CI_AS
        , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

The observed behavior is that the following fields do not observe either the server collation, or the database collation; they are always presented in Latin1_General_CI_AS_KS_WS collation.

On SQL Server 2012, we can use sys.sp_describe_first_result_set to easily obtain metadata about the columns returned from a particular query. I used the following to determine the collation mismatch:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
SELECT 
    DatabaseName                    = CONVERT(VARCHAR(50), d.name)
    , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc) 
    , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
FROM sys.databases d
WHERE name = DB_NAME();
';

EXEC sp_describe_first_result_set @command = @cmd;

The results:

enter image description here

Why is the collation of these columns statically set?

17

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 try to perform a PIVOT or UNPIVOT, the rules are even stricter (the output types must all match exactly rather than merely be compatible), but the error message is far less helpful, and even misleading:

Msg 8167, Level 16, State 1
The type of column "column name" conflicts with the type of other columns specified in the UNPIVOT list.

You need to work around these errors using explicit COLLATE clauses in your queries. For example, the union above could be:

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

The only time this may cause issues is you'll get confusing output if a collation is forced but doesn't contain the same character representation, or if sorting is used and the forced collation uses a different sort order than the source.

7

Background on Collation Precedence

The behavior you are seeing with regards to the Collation of various fields in the system catalog views is a result of how each field is defined and Collation Precedence.

When looking at sys.databases, it is important to keep in mind that it is not a table. While in the past (I think ending at SQL Server 2000) these were system catalog tables, they are now system catalog views. Hence, the source of the information in them is not necessarily coming from the current database context (or the context of the specified database when dealing with a fully-qualified object such as master.sys.databases).

Dealing specifically with sys.databases, some of the fields are coming from the [master] database (which was created with a collation based on the instance's default collation -- i.e. server-level collation), some of the fields are expressions (i.e. CASE statements), and some are coming from a "hidden" source: the [mssqlsystemresource] database. And the [mssqlsystemresource] database has a collation of: Latin1_General_CI_AS_KS_WS.

The name field is sourced from the name field in master.sys.sysdbreg. So this field should always be in the collation of the [master] database, which again will match the server's collation.

BUT, the following fields in sys.databases come from the [name] field in [mssqlsystemresource].[sys].[syspalvalues]:

  • user_access_desc
  • snapshot_isolation_state_desc
  • recovery_model_desc
  • page_verify_option_desc
  • log_reuse_wait_desc
  • containment_desc

Those fields should always have a collation of Latin1_General_CI_AS_KS_WS.

The collation_name field, however, comes from the following expression:

CONVERT(nvarchar(128),
        CASE
            WHEN serverproperty('EngineEdition')=5
                   AND [master].[sys].[sysdbreg].[id] as [d].[id]=(1)
              THEN serverproperty('collation')
            ELSE collationpropertyfromid(
                           CONVERT(int,
                            isnull([master].[sys].[sysobjvalues].[value] as [coll].[value],
                                   CONVERT_IMPLICIT(sql_variant,DBPROP.[cid],0)
                                ),
                         0),'name')
         END,
        0)

This is where Collation Precedence starts to come in. Both options for output here are system functions: serverproperty() and collationpropertyfromid(). The collation of this expression is considered a "Coercible-default":

Any Transact-SQL character string variable, parameter, literal, or the output of a catalog built-in function, or a built-in function that does not take string inputs but produces a string output.

If the object is declared in a user-defined function, stored procedure, or trigger, the object is assigned the default collation of the database in which the function, stored procedure, or trigger is created. If the object is declared in a batch, the object is assigned the default collation of the current database for the connection.

In light of that 2nd paragraph, since sys.databases is a view that exists in the master database, it takes on the collation of the master database (not the current database).

The state_desc field is also an expression:

CASE
   WHEN serverproperty('EngineEdition')=5
       AND [Expr1081]=(1)
       THEN N'RESTORING'
   ELSE
      CASE
         WHEN serverproperty('EngineEdition')=5
            AND CONVERT(bit,
                        [master].[sys].[sysdbreg].[status] as [d].[status]&(128),
                        0)=(1)
          THEN N'COPYING'
         ELSE
            CASE
               WHEN serverproperty('EngineEdition')=5
                  AND CONVERT(bit,
                              [master].[sys].[sysdbreg].[status] as [d].[status]&(256),
                              0)=(1)
                 THEN N'SUSPECT'
            ELSE [mssqlsystemresource].[sys].[syspalvalues].[name] as [st].[name]
            END
         END
       END

But, the collation on this expression is Latin1_General_CI_AS_KS_WS. Why? Well, something new is introduced in this expression: a reference to a real field: [mssqlsystemresource].[sys].[syspalvalues].[name] in that final ELSE clause. Column references are considered "Implicit":

A column reference. The collation of the expression is taken from the collation defined for the column in the table or view.

Of course, this opens up an interesting question: is it possible for this expression to return a different collation depending on how the CASE is evaluated? The literals will be in the collation of the database where this object is defined, but the ELSE condition returns a field value that should retain its original collation. Fortunately, we can simulate a test using the sys.dm_exec_describe_first_result_set Dynamic Management Function:

-- Force ELSE condition
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = -1;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE [name]
       END AS [Stuff]
FROM msdb.dbo.sysjobs
', NULL, NULL) rs

-- Force WHEN condition
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = 100;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE [name]
       END AS [Stuff]
FROM msdb.dbo.sysjobs
', NULL, NULL) rs

-- Control test
SELECT system_type_name, max_length, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @A INT;
SET @A = 100;
SELECT CASE WHEN @A = 100 THEN N''All About the Benjamins''
            ELSE N''Whazzup, yo?!?!?''
       END AS [Stuff]
', NULL, NULL) rs

Returns (on an instance set up with a collation of SQL_Latin1_General_CP1_CI_AS but running in a database with a collation of Japanese_Unicode_CI_AS):

system_type_name    max_length    collation_name
----------------    ----------    --------------
nvarchar(128)       256           SQL_Latin1_General_CP1_CI_AS
nvarchar(128)       256           SQL_Latin1_General_CP1_CI_AS
nvarchar(23)         46           Japanese_Unicode_CI_AS

Here we see that the two queries that reference the field in [msdb] take on the collation of the [msdb] database (which, being a system DB, was determined by the server collation).

Relating back to the Original Question

The observed behavior is that the following fields do not observe either the server collation, or the database collation; they are always presented in Latin1_General_CI_AS_KS_WS collation.

Your observation is spot-on: those fields always have a collation of Latin1_General_CI_AS_KS_WS, regardless of server collation or database collation. And the reason is: Collation Precedence. Those fields come from a table in the [mssqlsystemresource] database, and will retain that initial collation unless overridden with an explicit COLLATE clause since that has the highest precedence:

Explicit = An expression that is explicitly cast to a specific collation by using a COLLATE clause in the expression.

Explicit takes precedence over implicit. Implicit takes precedence over Coercible-default:
Explicit > Implicit > Coercible-default

And the related question:

Why is the collation of these columns statically set?

It is not that they are statically set, nor that the other fields are somehow dynamic. All of the fields in all of those system catalog views are operating on the same rules of Collation Precedence. The reason that they appear to be more "static" than the other fields (i.e. they do not change even if you install SQL Server with a different default collation, which in turn creates the system databases with that default collation) is that the [mssqlsystemresource] database consistently has a collation of Latin1_General_CI_AS_KS_WS across any installation of SQL Server (or so it certainly appears). And this makes sense because otherwise it would be difficult for SQL Server to manage itself internally (i.e. if sorting and comparison rules used for internal logic changed based on installation).

How to see these specifics yourself

If you want to see the source of any field(s) in any of these system catalog views, just do the following:

  1. In a query tab in SSMS, enable the Query Option of "Include Actual Execution Plan" (CTRL-M)
  2. Execute a query selecting one field from one of the system catalog views (I recommend selecting just one field at a time since the execution plan is ridiculously large / complex for even just a single field, and will include references to many fields that you aren't selecting):

    SELECT recovery_model_desc FROM sys.databases;
    
  3. Go to the "Execution plan" tab
  4. Right-click in the graphical Execution plan area and select "Show Execution Plan XML..."
  5. A new tab in SSMS will open with a title similar to: Execution plan.xml
  6. Go to the Execution plan.xml tab
  7. Look for the first occurrence of an <OutputList> tag (it should be between lines 10 and 20 usually)
  8. There should be a <ColumnReference> tag. The attributes in that tag should either point to a specific field in a table, or point to an expression defined later in the plan.
  9. If the attributes point to a real field then you are done as it has all of the info. The following is what shows up for the recovery_model_desc field:

    <ColumnReference Database="[mssqlsystemresource]" Schema="[sys]"
                     Table="[syspalvalues]" Alias="[ro]" Column="name" />
    
  10. If the attributes point to an expression, such as if you instead selected the state_desc field, then you will initially find:

    <ColumnReference Column="Expr1024" />
    
  11. In this case, you need to look through the rest of the plan for the definition of Expr1024 or whatever # it comes up with. Just keep in mind that there could be several of these references, but the definition won't be in an <OutputList> block. However, it will have a <ScalarOperator> sibling element that contains the definition. The following is what shows up for the state_desc field:

    <ScalarOperator ScalarString="CASE WHEN serverproperty('EngineEdition')=5 AND [Expr1081]=(1) THEN N'RESTORING' ELSE CASE WHEN serverproperty('EngineEdition')=5 AND CONVERT(bit,[master].[sys].[sysdbreg].[status] as [d].[status]&amp;(128),0)=(1) THEN N'COPYING' ELSE CASE WHEN serverproperty('EngineEdition')=5 AND CONVERT(bit,[master].[sys].[sysdbreg].[status] as [d].[status]&amp;(256),0)=(1) THEN N'SUSPECT' ELSE [mssqlsystemresource].[sys].[syspalvalues].[name] as [st].[name] END END END">
    

The same can be done to check the source of database-level catalog views as well. Doing this for an object like sys.tables will show that the name field comes from [current_db].[sys].[sysschobjs] (which is why it has a collation matching the database's collation), while the lock_escalation_desc field comes from [mssqlsystemresource].[sys].[syspalvalues] (which is why it has a collation of Latin1_General_CI_AS_KS_WS).

Clippy says, "It looks like you want to do an UNPIVOT query."

Now that we know why what Collation Precedence is and how it works, let's apply that knowledge to the UNPIVOT query.

For an UNPIVOT operation, SQL Server seems to really prefer (meaning: requires) that each source field be of the exact same type. Usually "type" refers to the base type (i.e. VARCHAR / NVARCHAR / INT / etc) but here SQL Server is also including the COLLATION. This should not be seen as unreasonable given what Collations control: the character set (i.e. Code Page) for VARCHAR, and the linguistic rules that determine equivalence of characters and combinations of characters (i.e. normalization). The following is a mimi-primer on what Unicode "normalization" is:

PRINT '---';
IF (N'aa' COLLATE Danish_Greenlandic_100_CI_AI = N'å' COLLATE Danish_Greenlandic_100_CI_AI)
     PRINT 'Danish_Greenlandic_100_CI_AI';
IF (N'aa' COLLATE SQL_Latin1_General_CP1_CI_AI = N'å' COLLATE SQL_Latin1_General_CP1_CI_AI)
     PRINT 'SQL_Latin1_General_CP1_CI_AI';
PRINT '---';
IF (N'of' COLLATE Upper_Sorbian_100_CI_AI =  N'öf' COLLATE Upper_Sorbian_100_CI_AI)
     PRINT 'Upper_Sorbian_100_CI_AI';
IF (N'of' COLLATE German_PhoneBook_CI_AI =  N'öf' COLLATE German_PhoneBook_CI_AI)
     PRINT 'German_PhoneBook_CI_AI';
PRINT '---';

Returns:

---
Danish_Greenlandic_100_CI_AI
---
Upper_Sorbian_100_CI_AI
---

So now let's start your original query. We will do a few tests to see how various changes alter the outcome, and then we will see how just a few changes can fix it.

  1. The first error is about the CompatibilityLevel field, which is the second field to be unpivoted, and just happens to be an expression containing all string literals. With no field references, the resulting collation is deemed a "coercible-default"). Coercible-defaults take on the collation of the current database, let's say SQL_Latin1_General_CP1_CI_AS. The next 20 or so fields are also only string literals and hence are also coercible-defaults, so they shouldn't be in conflict. But if we look back to the first field, recovery_model_desc, that is coming directly from a field in sys.databases, which makes it an "implicit" collation, and this does not take on the local DB's collation, but instead retains it's original collation, which is Latin1_General_CI_AS_KS_WS (because it is really coming from the [mssqlsystemresource] DB).

    So, if field 1 (RecoveryModel) is Latin1_General_CI_AS_KS_WS, and field 2 (CompatibilityLevel) is SQL_Latin1_General_CP1_CI_AS, then we should be able to force field 2 to be Latin1_General_CI_AS_KS_WS to match field 1, and then the error should appear for field 3 (AutoClose).

    Add the following to the end of the CompatibilityLevel line:
    COLLATE Latin1_General_CI_AS_KS_WS

    And then run the query. Sure enough, the error now states that it is the AutoClose field that has the conflict.

  2. For our second test we need to undo the change we just made (i.e. remove the COLLATE clause from the end of the CompatibilityLevel line.

    Now, if SQL Server is truly evaluating in the order in which the fields are specified, we should be able to remove field 1 (RecoveryModel), which will cause the current field 2 (CompatibilityLevel) to be the field that sets the master collation of the resulting UNPIVOT. And the CompatibilityLevel field is a coercible-default which takes on the database collation, so the first error should be the PageVerify field, which is a field reference, which is an implicit collation retaining the original collation, which in this case is Latin1_General_CI_AS_KS_WS and which is not the collation of the current DB.

    So go ahead and comment out the line starting with , RecoveryModel in the SELECT (towards the top) and then comment out the RecoveryModel line in the UNPIVOT clause below and remove the leading comma from the following line for CompatibilityLevel so that you don't get a syntax error.

    Run that query. Sure enough, the error now states that it is the PageVerify field that has the conflict.

  3. For our third test, we need to undo the changes we just did to remove the RecoveryModel field. So go ahead and put back the comma, and uncomment those two other lines.

    Now we can go the other direction with forcing a collation. Rather than changing the collation of the coercible-default collation fields (which is most of them), we should be able to change the implicit collation fields to that of the current DB, right?

    So, much like our first test, we should be able to force the collation of field 1 (RecoveryModel) with an explicit COLLATE clause. But if we specify a particular collation and then run the query in a database with a different collation, the coercible-default collation fields will pick up the new collation which will then conflict with what we are setting this first field to. That seems like a pain. Fortunately, there is a dynamic way to deal with this. There is a pseudo collation called DATABASE_DEFAULT that picks up the current databases collation (just like the coercible-default fields do).

    Go ahead and add the following to the end of the line, towards the top, that starts with , RecoveryModel: COLLATE DATABASE_DEFAULT

    Run that query. Sure enough, the error states, once again, that it is the PageVerify field that has the conflict.

  4. For the final test, we don't need to undo any of the prior changes.

    All we need to do now to fix this UNPIVOT query is to add the COLLATE DATABASE_DEFAULT to the end of the remaining implicit collation fields: PageVerify and RestrictedAccess. While the Collation field is also an implicit collation, that field comes from the master database, which is typically in line with the "current" database. But, if you want to be safe so that this always just works, then go ahead add the COLLATE DATABASE_DEFAULT to the end of that field as well.

    Run that query. Sure enough, no errors. All it took to fix this query was adding COLLATE DATABASE_DEFAULT to end of 3 fields (required) and maybe 1 more (optional).

  5. Optional Test: Now that we finally have the UNPIVOT query working correctly, change just one of any of the field definitions beginning with CONVERT(VARCHAR(50), to instead be 51, as in: CONVERT(VARCHAR(51),.

    Run the query. You should get the same The type of column "X" conflicts with the type of other columns specified in the UNPIVOT list. error that you got when it was only the collation that was not matching.

    Getting the same error for both datatype and collation mismatches is not specific enough to be truly helpful. So there is definitely room for improvement there :).


Note related to the Query more than to the specific Question about Collation:

Since all of the source fields are of the datatype NVARCHAR, it would be safer to CONVERT all output fields to NVARCHAR instead of VARCHAR. You might not be dealing with data at the moment that has any non-standard-ASCII characters, but the system meta-data does allow for them so converting to NVARCHAR(128) -- which is the largest max length of any of those fields -- at least guarantees that there wouldn't be a problem for you in the future, or for anyone else copying this code who might already have some of those characters in their system.

5

This is a workaround for the specific issue rather than a full answer to the question. You can avoid the error by converting to sql_variant rather than varchar(50):

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

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
    , [BaseType] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'BaseType')
    , [MaxLength] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'MaxLength')
    , [Collation] = SQL_VARIANT_PROPERTY(unpvt.OptionValue, 'Collation')
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(sql_variant, d.recovery_model_desc)
        , CompatibilityLevel            = CONVERT(sql_variant, 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(sql_variant, CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(sql_variant, CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(sql_variant, CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(sql_variant, CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(sql_variant, CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(sql_variant, CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(sql_variant, CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(sql_variant, CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(sql_variant, CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(sql_variant, CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(sql_variant, CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(sql_variant, CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(sql_variant, CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(sql_variant, CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(sql_variant, CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(sql_variant, CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(sql_variant, CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(sql_variant, CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(sql_variant, CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(sql_variant, CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(sql_variant, 'TRUE')
        , PageVerify                    = CONVERT(sql_variant, page_verify_option_desc  )
        , BrokerEnabled                 = CONVERT(sql_variant, CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(sql_variant, CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(sql_variant, CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(sql_variant, user_access_desc)
        , Collation                     = CONVERT(sql_variant, d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

I have added three columns for information about the underlying type of the OptionValue column.

Sample output

If the client cannot handle sql_variant data, do a final (top level) convert on the unpvt.OptionValue column, to e.g. nvarchar(256).

4

Ok, so I took a look at

sp_helptext [sys.databases]

then broke down where the columns were coming from. The ones with the Latin1_General_CI_AS_KS_WS collation are all coming from the system table sys.syspalvalues which appears to be a generic lookup table (it's a system table, so you will have to connect via the DAC in order to see it.).

My guess is that it is set to Latin1_General_CI_AS_KS_WS to handle any possible lookup values. I can see how it would be annoying though.

Another way to see the definition (originally provided by Max in a comment) is:

SELECT ObjectSchema = s.name
    , ObjectName = o.name
    , ObjectDefinition = sm.definition
FROM master.sys.all_sql_modules sm
    INNER JOIN master.sys.system_objects o ON sm.object_id = o.object_id
    INNER JOIN master.sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'sys' 
    AND o.name = 'databases';`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.