james marriot (1/24/2013)
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(.....)
Typically where clause. The point of an index is to reduce the rows in consideration for the query as fast as possible. With InvoiceID first in both indexes, you've got no way to do an index seek on one of the filters (the where clause) and then a join, if SQL wants to filter before the join it has to do an index scan.
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?
That one mostly comes down to test it and see. There's a huge number of factors involved, the join types (physical operator), the number of rows in the outer resultset, the number of rows that will match in a join, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability