SQL Server带WHERE子句查询时为何选聚集索引查找而非非聚集索引扫描？

创建表 #

CREATE TABLE  AGENTS 
( 
    AGENT_CODE CHAR(6) NOT NULL PRIMARY KEY, 
    AGENT_NAME CHAR(40), 
    WORKING_AREA CHAR(35), 
    COMMISSION INT, 
    PHONE_NO CHAR(15), 
    COUNTRY NVARCHAR(25) 
);

插入数据 #

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', '15', '077-25814763', '');
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', '13', '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', '12', '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '15', '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '14', '007-22388644', '');
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', '12', '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', '13', '045-21447739', '');
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '14', '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '11', '029-12358964', '');
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', '15', '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', '15', '008-22544166', '');
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '11', '008-22536178', '');

创建非聚集索引 #

在AGENT_CODE列创建包含AGENT_NAME的非聚集索引：

CREATE NONCLUSTERED INDEX [NON_CLUS_IX_AGENTS_AGENTCODE_AGENTNAME]
ON [dbo].[AGENTS] ([AGENT_CODE])
INCLUDE (AGENT_NAME)

问题 #

当仅查询AGENT_CODE和AGENT_NAME列且带WHERE子句时，SQL Server使用聚集索引查找而非非聚集索引扫描；但无WHERE子句查询时则使用非聚集索引扫描。请问：即便非聚集索引包含查询所需全部列，为何带WHERE子句时SQL Server不使用该索引？

解答 #

首先要明确：SQL Server中主键默认会创建聚集索引，你的AGENT_CODE是主键，所以聚集索引本身就是按AGENT_CODE有序存储的。

带WHERE子句时的选择逻辑 #

当通过WHERE子句精确匹配AGENT_CODE时，比如WHERE AGENT_CODE = 'A007'：

• 聚集索引查找可以直接通过主键值快速定位到目标行，不需要额外的跳转或数据拼接。
• 你的非聚集索引虽然包含了查询所需的全部列，但对于这种精确匹配场景，它的查找成本和聚集索引相比并没有优势——甚至因为额外维护了一套索引结构，查询优化器会认为走聚集索引更直接。再加上你的表数据量极小（仅12条），两种方式的开销差异可以忽略，优化器最终会选择更原生的聚集索引查找路径。

无WHERE子句时的选择逻辑 #

当需要返回所有行的AGENT_CODE和AGENT_NAME时：

• 非聚集索引的体积远小于聚集索引（聚集索引包含表的所有列，非聚集索引只包含指定的两列），扫描非聚集索引的IO开销更低，所以优化器会选择扫描非聚集索引来获取数据，以减少资源消耗。

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