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:
Why is the collation of these columns statically set?
Best Answer
The official word from Microsoft:
You asked (emphasis mine):
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:
Whereas if server collation were case sensitive, the query above would return 0 rows, just like this does:
For example, if you install an instance of SQL Server with
SQL_Estonian_CP1257_CS_AS
collation, then run the following:You will see these results (or something similar, depending on your version of SQL Server):
Now, to demonstrate metadata views that inherit database collation, rather than inheriting server collation from the master database:
Results:
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:You get this error:
Similarly, if you try to perform a
PIVOT
orUNPIVOT
, 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:You need to work around these errors using explicit
COLLATE
clauses in your queries. For example, the union above could be: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.