Bhuvnesh (1/24/2013)
Order table will have index on InvoiceID and InvoiceNumberOrderLines will have ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
Not the most efficient order for the columns within the indexes, will probably result in sub-optimal performance, probably index scans rather than index seeks
and cardinality high for left most column will work better,like sequence InvoiceId, ProductID,status
Selectivity is only a portion of the story, unfortunately it's the only portion that most people remember.
The point of a index is to reduce the rows in consideration for the query as early as possible. As such, you want leading columns of indexes to be columns that you filter on, not the most selective column in the table.
An index that leads with InvoiceID is not going to be very useful for a query that filters on Status and another column.
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