带聚集索引主键的表中非聚集索引结构：显式添加主键列至索引键的场景及存储位置解析

先来看基础的表结构与创建的三个非聚集索引：

表结构 #

CREATE TABLE [dbo].[Users] (
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Age] [INT] NULL,
    [CreationDate] [DATETIME] NOT NULL,
    [DisplayName] [NVARCHAR](40) NOT NULL,
    [Location] [NVARCHAR](100) NULL,
    CONSTRAINT [PK_Users_Id] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

三个非聚集索引 #

-- 索引#1
CREATE INDEX IX_CreationDate_Id_DisplayName_Age1 ON dbo.Users(CreationDate, Id) INCLUDE (DisplayName, Age);
-- 索引#2
CREATE INDEX IX_CreationDate_Id_DisplayName_Age2 ON dbo.Users(CreationDate) INCLUDE (DisplayName, Age);
-- 索引#3
CREATE INDEX IX_CreationDate_Id_DisplayName_Age3 ON dbo.Users(CreationDate) INCLUDE (ID, DisplayName, Age);

已知Id作为聚集索引主键，会自动包含在所有非聚集索引中，接下来针对两个问题逐一解答：

1. 哪些场景下需要像索引#1那样，将Id列显式添加为非聚集索引键的一部分？ #

主要有以下几个实用场景：

• 需要多列排序的查询场景：如果你的业务查询经常需要ORDER BY CreationDate, Id，把Id加到索引键里，SQL Server可以直接利用索引的有序性返回结果，无需额外执行排序操作，性能会明显提升。比如SELECT DisplayName FROM Users ORDER BY CreationDate, Id这类查询，索引#1能完美覆盖需求。
• 复合条件过滤的查询场景：当查询同时基于CreationDate和Id做范围或等值过滤时，比如SELECT Age FROM Users WHERE CreationDate >= '2023-01-01' AND Id < 1000，将Id作为索引键的第二列，SQL Server可以借助索引的有序性快速缩小数据范围，提升查询效率。
• 处理重复键值的场景：如果CreationDate存在大量重复值，把唯一的Id加入索引键后，能让整个索引的键值变得唯一（因为Id是主键）。这样在查找特定CreationDate下的单条记录时，定位会更精准，同时SQL Server维护索引时，处理重复键的逻辑也会更高效。

2. 在上述三个索引的B-Tree结构中，Id列分别存储在什么位置？ #

咱们逐个拆解：

• 索引#1：Id是索引键的组成部分，会存储在B-Tree的所有层级（根节点、中间节点、叶子节点）的索引键条目里。整个B-Tree的排序逻辑是先按CreationDate，再按Id。
• 索引#2：Id没有显式出现在索引键或INCLUDE列表中，但因为它是聚集主键，SQL Server会自动把它作为叶子节点的行定位器存储在非聚集索引的叶子节点里（用来指向聚集索引对应的行），不会出现在根节点和中间节点中。
• 索引#3：这里显式把Id加到了INCLUDE列表，但实际上因为Id是聚集主键，本来就会自动包含在叶子节点中。所以Id依然存储在叶子节点里——显式INCLUDE它属于多此一举，不会改变存储位置，也不会带来额外的存储或性能变化。

内容的提问来源于stack exchange，提问作者Murali Dhar Darshan