Home Forums SQL Server 2008 SQL Server 2008 - General What is proper way of indexing on a table which contain large amount of data RE: What is proper way of indexing on a table which contain large amount of data

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