为什么在创建索引时使用 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中，而只在中的列列表中code>SELECT 子句是您使用 INCLUDE 的地方.

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

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 columns 不是索引中的关键列，因此它们没有排序.这意味着它对于我上面提到的谓词、排序等并不是真的有用.但是，如果您在关键列的几行中进行残差查找，它可能会很有用

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屋！