The reasons why (including the data in the leaf level of the index) have been nicely explained. The reason that you give two shakes about this, is that when you run your query, if you don't have the additional columns included (new feature in SQL 2005) the SQL Server has to go to the clustered index to get the additional columns which takes more time, and adds more load to the SQL Server service, the disks, and the memory (buffer cache to be specific) as new data pages are loaded into memory, potentially pushing other more often needed data out of the buffer cache.

如果列不在WHERE/JOIN/GROUP BY/ORDER BY中，而只在SELECT子句中的列列表中使用INCLUDE。

INCLUDE子句将数据添加到最低的/叶级，而不是添加到索引树中。 这使得索引更小，因为它不是树的一部分

INCLUDE列不是索引中的键列，因此它们没有排序。 这意味着它对于我上面提到的谓词、排序等并不是很有用。但是，如果从键列开始的几行中有残留查找，那么它可能会很有用。

另一篇MSDN文章提供了一个实际示例

您可以使用INCLUDE将一个或多个列添加到非聚集索引的叶级，如果这样做可以“覆盖”您的查询。

假设您需要查询员工的ID、部门ID和姓。

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

如果碰巧在(EmployeeID, DepartmentID)上有一个非聚集索引，一旦找到给定部门的员工，现在必须执行“书签查找”以获得实际的完整员工记录，只是为了获得姓氏列。如果你有很多员工的话，这在绩效方面是非常昂贵的。

如果你在你的索引中包含了这个姓氏:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

然后，您需要的所有信息都可以在非聚集索引的叶级中获得。只需在非聚类索引中查找给定部门的员工，就可以获得所有必要的信息，并且不再需要在索引中查找每个员工的书签—>可以节省大量时间。

显然，您不能在每个非聚集索引中包含每一列——但是如果您确实有一些查询只需要“覆盖”一到两列(并且经常使用)，那么将这些查询包含到合适的非聚集索引中会非常有帮助。

我在已经给出的答案中没有看到的另一个考虑因素是，包含的列可能是不允许作为索引键列的数据类型，例如varchar(max)。

这允许您在覆盖索引中包含这样的列。我最近不得不这样做，以提供一个nHibernate生成的查询，它在SELECT中有很多列，有一个有用的索引。

The reasons why (including the data in the leaf level of the index) have been nicely explained. The reason that you give two shakes about this, is that when you run your query, if you don't have the additional columns included (new feature in SQL 2005) the SQL Server has to go to the clustered index to get the additional columns which takes more time, and adds more load to the SQL Server service, the disks, and the memory (buffer cache to be specific) as new data pages are loaded into memory, potentially pushing other more often needed data out of the buffer cache.

There is a limit to the total size of all columns inlined into the index definition. That said though, I have never had to create index that wide. To me, the bigger advantage is the fact that you can cover more queries with one index that has included columns as they don't have to be defined in any particular order. Think about is as an index within the index. One example would be the StoreID (where StoreID is low selectivity meaning that each store is associated with a lot of customers) and then customer demographics data (LastName, FirstName, DOB): If you just inline those columns in this order (StoreID, LastName, FirstName, DOB), you can only efficiently search for customers for which you know StoreID and LastName.

另一方面，在StoreID上定义索引并包括LastName、FirstName、DOB列将允许您在本质上进行两次查找——在StoreID上进行索引谓词，然后在任何包含的列上进行查找谓词。这将让你覆盖所有可能的搜索排列，只要它开始与StoreID。