SQL Server中的综合索引与INCLUDE覆盖索引

I understand that Composite Indexes are always used Left to Right (e.g. if an Index was on City, State, WHERE City = "Blah" or WHERE City = "Blah" AND State = "AA" would work fine but WHERE State = "AA" would not).

我知道综合指数总是从左到右使用（例如，如果指数在城市，州，WHERE城市=“Blah”或WHERE City =“Blah”AND St​​ate =“AA”将正常工作但WHERE State =“AA “ 不会）。

Does this same principle apply to INCLUDE indexes?

这个原理是否同样适用于INCLUDE索引？

Thanks in advance!

提前致谢！

Clay

粘土

2 个解决方案

#1

Include columns can only be used to supply columns to the SELECT portion of the query. They cannot be used as part of the index for filtering.

包含列只能用于向查询的SELECT部分​​提供列。它们不能用作过滤索引的一部分。

EDIT: To further clarify my point, consider this example:

编辑：为了进一步澄清我的观点，请考虑以下示例：

I create a simple table and populate it:

我创建一个简单的表并填充它：

create table MyTest (
    ID int,
    Name char(10)
)

insert into MyTest
    (ID, Name)
    select 1, 'Joe' union all
    select 2, 'Alex'

Now consider these 3 indexes and their corresponding execution plans for a simple SELECT.

现在考虑这三个索引及其相应的执行计划，用于简单的SELECT。

select ID, Name
    from MyTest
    where Name = 'Joe'

Case 1: An index on just ID results in a TABLE SCAN.

情况1：只有ID的索引导致TABLE SCAN。

create index idx_MyTest on MyTest(ID)

Case 2: An index on ID including name. Somewhat better because the index covers the query, but I still get a SCAN operation.

案例2：ID的索引，包括名称。有点好，因为索引涵盖了查询，但我仍然得到一个SCAN操作。

create index idx_MyTest on MyTest(ID) include (Name)

Case 3: An index on Name including ID. This is the best. The index is built on the column in my WHERE clause, so I get a SEEK operation, and the index covers the query because of the included column.

案例3：名称包括ID的索引。这是最好的。索引建立在我的WHERE子句中的列上，因此我得到了一个SEEK操作，并且由于包含列，索引覆盖了查询。

create index idx_MyTest on MyTest(Name) include (ID)

#2

No, include fields are not ordered.

不，包含字段未订购。

Here are some additional design considerations:

以下是一些其他设计注意事项：

http://msdn.microsoft.com/en-us/library/ms190806.aspx

http://msdn.microsoft.com/en-us/library/ms190806.aspx