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

前端之家收集整理的这篇文章主要介绍了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;

结果:

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

解决方法

来自微软的官方消息:

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.

你问(强调我的):

Why is the collation of these columns statically set?

某些列静态设置的原因是查询不需要担心服务器或数据库排序规则(更重要的是:CaSe SenSiTIviTy)才能正常工作.无论整理如何,此查询始终有效:

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

如果服务器排序规则区分大小写,那么上面的查询将返回0行,就像这样:

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

例如,如果使用sql_Estonian_CP1257_CS_AS排序规则安装sql Server实例,请运行以下命令:

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

您将看到这些结果(或类似的东西,具体取决于您的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

现在,要演示继承数据库排序规则的元数据视图,而不是从master数据库继承服务器排序规则:

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

结果:

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

因此,您可以看到,在这种情况下,多个列继承数据库排序规则,而其他列则固定为此“通用”Latin1排序规则,这意味着它用于将某些名称属性与区分大小写问题隔离,如上所述.

如果您尝试执行UNION,例如:

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

你收到这个错误

Msg 451,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.

类似地,如果您尝试执行PIVOT或UNPIVOT,规则甚至更严格(输出类型必须完全匹配而不仅仅是兼容),但错误消息远没有帮助,甚至误导:

Msg 8167,State 1

The type of column “column name” conflicts with the type of other columns specified in the UNPIVOT list.

您需要使用查询中的显式COLLATE子句来解决这些错误.例如,上面的联合可以是:

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;

这可能导致问题的唯一时间是,如果强制排序但不包含相同的字符表示,或者如果使用排序并且强制排序使用与源不同的排序顺序,则会使输出混乱.

原文链接:https://www.f2er.com/mssql/79464.html

猜你在找的MsSQL相关文章