为什么在创建索引时使用INCLUDE子句？ [英] Why use the INCLUDE clause when creating an index?

问题描述

在参加70-433考试时，我注意到你可以通过以下两种方式之一创建覆盖指数。

While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways.

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

- 或者 -

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

INCLUDE条款对我来说是新的。为什么要使用它以及在确定是否使用INCLUDE子句创建覆盖索引时建议使用什么准则？

The INCLUDE clause is new to me. Why would you use it and what guidelines would you suggest in determining whether to create a covering index with or without the INCLUDE clause?

推荐答案

如果该列不在 WHERE / JOIN / GROUP BY / ORDER BY 中，但仅在 SELECT 子句。

If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.

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

The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree

INCLUDE列不是键列在索引中，所以他们没有订购。
这意味着它对于谓词，排序等并不是很有用，如上所述。但是，如果在键列的几行中有剩余查找，则可能

INCLUDE columns are not key columns in the index, so they are not ordered. This means it isn't really useful for predicates, sorting etc as I mentioned above. However, it may be useful if you have a residual lookup in a few rows from the key column(s)

另一篇MSDN文章一个有效的例子

这篇关于为什么在创建索引时使用INCLUDE子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！