使用include列创建索引的差异或不使用

问题描述

限时删除！！

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

I Want To Create Index In SQL Server 2008 R2 in Column1 and Column2What 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.

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

1403页，肝出来的..