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

此刻,要演示担任数据库排序法则的元数据视图,而不是从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;

这也许导致题目的独一时刻是,假如逼迫排序但不包括沟通的字符暗示,可能假如行使排序而且逼迫排序行使与源差异的排序次序,则会使输出紊乱.

（编辑：湖南网）

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