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

在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

（编辑：荆州站长网）

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