sql-server – sys.databases中某些列的排序规则是什么？

我试图在各种版本的SQL Server上运行sys. databases中包含的各个列的UNPIVOT,范围从2005年到2012年.

UNPIVOT失败,出现以下错误消息：

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.

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;

这旨在为给定数据库提供格式良好的数据库选项列表,类似于：

+----------+----------------------------+----------------------------+
| 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 |
+----------+----------------------------+----------------------------+

当我在具有Latin1_General_CI_AS_KS_WS排序规则的服务器中运行此命令时,语句成功.如果我修改T-SQL以便某些字段具有COLLATE子句,它将在具有其他排序规则的服务器上运行.

在Latin1_General_CI_AS_KS_WS以外的排序规则的服务器上运行的代码是：

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

SELECT [Database]            = unpvt.DatabaseName,d.recovery_model_desc) COLLATE SQL_Latin1_General_CP1_CI_AS,page_verify_option_desc  ) COLLATE SQL_Latin1_General_CP1_CI_AS,user_access_desc) COLLATE SQL_Latin1_General_CP1_CI_AS,Collation
    )
) AS unpvt;

观察到的行为是以下字段不遵守服务器排序规则或数据库排序规则;它们始终以Latin1_General_CI_AS_KS_WS排序规则显示.

在SQL Server 2012上,我们可以使用sys.sp_describe_first_result_set轻松获取有关从特定查询返回的列的元数据.我使用以下内容来确定排序规则不匹配：

DECLARE @cmd NVARCHAR(MAX);

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

EXEC sp_describe_first_result_set @command = @cmd;

结果：

为什么静态设置这些列的排序规则？

解决方法

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!