Composite Index vs. INCLUDE Covering Index in SQL Server

Question

Hi Folks

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).

Does this same principle apply to INCLUDE indexes?

Thanks in advance!

Clay

Answer 1

No, include fields are not ordered.

Here are some additional design considerations:

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

Answer 2

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.

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 ID, Name
    from MyTest
    where Name = 'Joe'

Case 1: An index on just ID results in a 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.

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.

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