james marriot (1/24/2013)

---

I have an example where the table im indexing appears in the join and where clause:

select OL1.a, OL1.b, OL1.c, OL1.d, OL1.e, OL1.f, OL1.g

from Orders O

inner join OrderLines OL1

inner join OL1 on O.InvoiceID = OL1.InvoiceID

inner join Product P on P.ProductID = OL1.ProductID

WHERE (OL1.h is not null) and (O.InvoiceNumber = 1001)

and (OL1.status = 1)

Ive been trying a few indexes but im not sure entirely which is best. i really would like to ask:

1. when a table is in the join and where clause, do i create the index with the join col first, or the where col first ?

e.g create ix_test on orderLines(status, InvoiceId, ProductID) Include(.....)

or

e.g create ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)

2. where there are two columns joined upon, which should generally come first in the index definition? is it based on cardinality? if so is it high or low that decides what col comes first?

Thanks!