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

（编辑：湖南网）

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