SQL Server中如何查看非聚集索引隐含的聚集索引键？

在SQL Server中，当表存在聚集索引时，非聚集索引会自动将聚集索引键作为隐含行定位器包含在内，但这些隐含列不会在常规的sys.index_columns元数据查询中直接显示。要查看这些隐含的键，你可以通过对比聚集索引的键列与非聚集索引的显式列，找出未被非聚集索引显式包含的聚集键列——这些就是自动添加的隐含键。

实现步骤 #

1. 先获取目标表的聚集索引键列
2. 对比非聚集索引的显式列，筛选出隐含的聚集键

完整查询语句 #

USE tempdb;
GO

-- 第一步：存储目标表的聚集索引键列ID
DECLARE @clustered_key_columns TABLE (column_id INT);
INSERT INTO @clustered_key_columns
SELECT ic.column_id
FROM sys.indexes i
JOIN sys.index_columns ic 
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id
WHERE 
    i.object_id = OBJECT_ID('dbo.abc') 
    AND i.type = 1 -- 仅筛选聚集索引
    AND ic.key_ordinal > 0; -- 仅筛选索引键列（排除包含列）

-- 第二步：查询非聚集索引的显式列 + 隐含聚集键列
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS 表名,
    i.name AS 索引名,
    i.type_desc AS 索引类型,
    COL_NAME(ic.object_id, ic.column_id) AS 列名,
    '显式索引列' AS 列类型
FROM sys.tables t
JOIN sys.indexes i 
    ON t.object_id = i.object_id
JOIN sys.index_columns ic 
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id
WHERE 
    t.object_id = OBJECT_ID('dbo.abc') 
    AND i.type != 1 -- 排除聚集索引
UNION ALL
-- 追加隐含的聚集索引键列
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS 表名,
    i.name AS 索引名,
    i.type_desc AS 索引类型,
    COL_NAME(t.object_id, ck.column_id) AS 列名,
    '隐含聚集索引键' AS 列类型
FROM sys.tables t
JOIN sys.indexes i 
    ON t.object_id = i.object_id
CROSS JOIN @clustered_key_columns ck
LEFT JOIN sys.index_columns ic 
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id 
    AND ic.column_id = ck.column_id
WHERE 
    t.object_id = OBJECT_ID('dbo.abc') 
    AND i.type != 1 -- 排除聚集索引
    AND ic.column_id IS NULL -- 筛选非聚集索引未显式包含的聚集键列
ORDER BY 表名, 索引名, 列类型 DESC;

查询结果说明 #

• 结果会分为两部分：非聚集索引的显式索引列和隐含聚集索引键
• 以你提供的测试表为例，非聚集索引IX_2的结果会显示col3（显式列）和col1（隐含聚集键）

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