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

发布时间：2021-05-24 10:30:28 所属栏目：MsSql教程来源：网络整理

导读：我试图在各种版本的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 column

现在,要演示继承数据库排序规则的元数据视图,而不是从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;

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

（编辑：上饶站长网）

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

5/5

首页