SQL Server选择了非聚集索引而不是聚集索引

聚集索引不仅包含索引的key值，还包含表数据；
非聚集索引只包含索引的key值。

SQL Server在有些情况下，有聚集索引和非聚集索引存在时，会选择走非聚集索引，而不走聚集索引。

例子如下：

在 SQL Server 的adventureWorks数据库下，运行如下语句：

select DepartmentID,Name from HumanResources.Department

Department表上有两个索引，一个是departmentid上的聚集索引，另一个是name上的非聚集索引；
该语句的运行结果如下：

DepartmentID    Name
12    Document Control
1    Engineering
16    Executive
14    Facilities and Maintenance
10    Finance
9    Human Resources
11    Information Services
4    Marketing
7    Production
8    Production Control
5    Purchasing
13    Quality Assurance
6    Research and Development
3    Sales
15    Shipping and Receiving
2    Tool Design

可以看到结果是按在name的升序排列的，说明使用的是name上的非聚集索引。

如果存在另外一个没有索引的列，则会选择走departmentid上的聚集索引，因为sql server会认为这样的速度比较快。

select DepartmentID,Name,GroupName from HumanResources.Department

DepartmentID    Name    GroupName
1    Engineering    Research and Development
2    Tool Design    Research and Development
3    Sales    Sales and Marketing
4    Marketing    Sales and Marketing
5    Purchasing    Inventory Management
6    Research and Development    Research and Development
7    Production    Manufacturing
8    Production Control    Manufacturing
9    Human Resources    Executive General and Administration
10    Finance    Executive General and Administration
11    Information Services    Executive General and Administration
12    Document Control    Quality Assurance
13    Quality Assurance    Quality Assurance
14    Facilities and Maintenance    Executive General and Administration
15    Shipping and Receiving    Inventory Management
16    Executive    Executive General and Administration

但是，如果把groupname也加入name列上的非聚集索引后，会发现，SQL Server又走非聚集索引了。

DepartmentID    Name    GroupName
12    Document Control    Quality Assurance
1    Engineering    Research and Development
16    Executive    Executive General and Administration
14    Facilities and Maintenance    Executive General and Administration
10    Finance    Executive General and Administration
9    Human Resources    Executive General and Administration
11    Information Services    Executive General and Administration
4    Marketing    Sales and Marketing
7    Production    Manufacturing
8    Production Control    Manufacturing
5    Purchasing    Inventory Management
13    Quality Assurance    Quality Assurance
6    Research and Development    Research and Development
3    Sales    Sales and Marketing
15    Shipping and Receiving    Inventory Management
2    Tool Design    Research and Development

造成这种现象的原因是由于非聚集索引只存储key值，所以会使用更少的page，这样sql server在生成执行计划时，便会优先考虑这个非聚集索引，因为这样的能更减少I/O。

但实际上，从执行计划分析，这个例子中走聚集索引与非聚集索引的CPU，I/O代价是完全一致的，估计是SQL Server设定的某种优先级吧。