使用include列创建索引的差异或不使用 [英] Difference of create Index by using include column or not using

问题描述

我想在Column1和Column2中的SQL Server 2008 R2中创建索引
以下查询有什么区别：

I Want To Create Index In SQL Server 2008 R2 in Column1 and Column2 What is the difference of below query:

1. 不包括

1. Not include

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[MyTable] 
(
    [Column1] ASC,
    [Column2] ASC
) ON [PRIMARY]

或者包括：

Or include:

CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[MyTable] 
(
    [Column1] ASC
)
INCLUDE ([Column2]) ON [PRIMARY]

推荐答案

在第一个 Column2 获取添加到索引键。在第二个中，它可能不会将^（*）添加到键中，在这种情况下它只会出现在索引叶页中。这可以允许通过 Column1 搜索索引，但是无需返回基表（书签查找/密钥查找）以检索 Column2 的值。

In the first one Column2 gets added to the index key. In the second one it might not ^(*) get added to the key in which case it will only appear in the index leaf pages. This can allow the index to be searched by Column1 but avoid the need to go back to the base table (bookmark lookup/key lookup) to retrieve the value for Column2.

ie它使index2覆盖查询，例如

i.e. it makes index2 "covering" for queries such as

SELECT Column1,Column2
FROM [dbo].[MyTable] 
WHERE Column1 = 'X'

它还包括诸如

SELECT Column1,Column2
FROM [dbo].[MyTable] 
WHERE Column1 = 'X' AND Column2 = 'Y' 

但是index1可能对第二个查询表现更好，因为它可以直接在两列上搜索（而不是只能搜索 Column1 ，然后需要在索引叶级别评估所有匹配的行，看看它们是否符合 Column2 谓词）。如果 Column2 从未用作针对该索引的搜索谓词，那么针对该索引的查询将无法从 Column2 order然后它应该作为 INCLUDE -d列添加，以保持键的大小减少并减少索引中的页数。

But index1 may well perform better for the second query as it can seek on the two columns directly (as opposed to only being able to search on Column1 then needing to evaluate all matching rows at the index leaf level to see if they meet the Column2 predicate). If Column2 is never used as a search predicate against that index and your queries against that index wouldn't benefit from having Column2 ordered then it should be added as an INCLUDE-d column to keep the size of the key down and reduce the number of pages in the index.

_{（*）我上面说可能没有的原因是因为如果 Column2 是聚合索引键的（部分）无论如何，仍然会被添加到那里对于未使用 UNIQUE 选项创建的非聚集索引。}

_{(*) The reason I say "might not" above is because if Column2 is (part of) the clustered index key it will still be added there anyway for a non clustered index not created with the UNIQUE option.}

这篇关于使用include列创建索引的差异或不使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！