如何理解Azure SQL Database中INFORMATION_SCHEMA.COLUMNS的排序规则？

执行的操作与错误 #

我执行了以下SQL操作：

CREATE TABLE #SourceColumns (Id INT NOT NULL, Field VARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL);

insert into #SourceColumns values (1, 'test')

select * from INFORMATION_SCHEMA.COLUMNS join #SourceColumns on COLUMN_NAME = Field;

得到错误：

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

疑问与尝试 #

我知道修复方法，但想先理解冲突原因。尝试用以下语句查看COLUMN_NAME的排序规则，却返回空结果：

SELECT c.name AS column_name, c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'INFORMATION_SCHEMA.COLUMNS'

冲突原因 #

排序规则冲突的本质是关联的两列排序规则不匹配：

• INFORMATION_SCHEMA.COLUMNS是系统内置视图，其中COLUMN_NAME列的默认排序规则为SQL_Latin1_General_CP1_CI_AS（SQL Server系统对象的标准排序规则）。
• 临时表#SourceColumns的Field列使用COLLATE DATABASE_DEFAULT，继承了当前数据库的排序规则Latin1_General_CI_AS。
  当JOIN条件中直接比较这两列时，SQL Server无法自动确定匹配用的排序规则，因此抛出冲突错误。

为什么查询sys.columns返回空结果？ #

INFORMATION_SCHEMA.COLUMNS是系统视图而非物理表，所以通过sys.tables找不到对应的对象。要查看该视图列的排序规则，可改用以下方法：

方法1：直接查询INFORMATION_SCHEMA本身

SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' 
  AND TABLE_NAME = 'COLUMNS';

方法2：关联sys.views查询

SELECT c.name AS column_name, c.collation_name
FROM sys.columns c
JOIN sys.views v ON c.object_id = v.object_id
WHERE v.name = 'COLUMNS' 
  AND SCHEMA_NAME(v.schema_id) = 'INFORMATION_SCHEMA';

内容的提问来源于stack exchange，提问作者Buda Florin