Once again, I have a situation where my way is again blocked.
As I said earlier in this post that I have to implement indexing on a group of tables. Before raising my question, I just want explain my situation again.
In my table (as I earlier explained), there are 2 bit type columns.
1. first one is for record Status. Record is active or not.
2. second one is for record state. We don't delete any record physically. We just change the state flag from 1 to 0.
3. there are few foreign key columns, contains int ID, which are referenced other base tables. Suppose record_cat, record_subcat1 & record_subcat2 column.
record_cat will also be included in most of the queries.
Now, my question is:
As I will have to include both status & state columns in each & every query. But these columns will return a very large number of rows. because approx 70% of records are active.
record_state: 85%
record_status: 78%
record_cat: 60%
record_subcat1: 40%
record_subcat2 : 20%
so,If I create a non clustered index like: record_state, record_status, record_cat, record_subcat1, record_subcat2
then first two columns will produce a large number of rows & then this is the responsibility of record_cat, record_subcat1 and record_subcat2 to filter out the data.
but if I consider record_subcat2, record_subcat1, record_cat, record_status, record_state, this sequence is also a covering index but in just a opposite scenario. means record_subcat2 just filter out most of the records and so on.
But If i consider your your recommendation (most selective column first) then record_state & record_status should be first.
please give me an advice.