0

I have a table with around 19 columns which contains reasonable large amount of data and is primarily being queried to retrieve data using select statements based on different where clause. Since this table is primarily queried to get data, I thought about creating Non Clustered indexes based on the different where clauses getting used in the queries. Also, all the get queries returns all the columns in the table as part of the select list. Based on the information above, I have two questions for selecting the indexes:

  1. let us assume that we have the following SPs which queries as:

where [col_a] = {value} and [col_b] = {value}

[col_b] = {value} and [col_a] = {value}

[col_a] = {value} and [col_c] = {value} and [col_d] = {value}

[col_a] = {value} and [col_c] = {value}

I have created the following Non Clustered indexes on the table as

[col_a] and [col_b] --> Would the first SP still use this index as the orders are reversed

[col_a] and [col_c] and [col_d] --> Would the last SP use this index as the first two columns match with order

Also, should we go ahead and try to define Non Clustered indexes based on the filter/join clauses for the get SPs on a table?

  1. Since the select list in all the SPs return the entire list of columns, I added all the columns of the table as included columns in the Non Clustered indexes(covering index) to avoid bookmark lookups. Is this approach correct? What are the space implications in this case since we are storing all the table columns as part of the index definition?

1 Answer 1

0

1: The order of index columns does not matter if both columns are used in your query (either in the WHERE clause or a JOIN condition).

2: A partial index can be used counting from the left: (a, b) can be used when searching on a alone.

Since column A is used in all 4 queries, I suggest you always include that in your indexes.

Next, column A and C are used together as well, so that yields your second index:

IX_1 (col_a, col_b)
IX_2 (col_a, col_c, col_d)

should give you optimal performance.

I assume one of these columns is the PK - if not, you need to create one.

Coverig indexes that include all columns are a very bad idea, because the PK lookup is much less expensive than the overhead incurred by having to maintain 2 complete data copies of the table.

A covering index that includes all columns is, of course, a copy of the entire table.

2
  • Adaptr, Thanks a lot for your explanation. It was very helpful. I have one question though with respect to the columns for covering index. What dictates the choice for the included columns selection criteria?
    – koder
    Jan 11, 2012 at 10:26
  • Say you have a 10GB table, with 100M rows. The PK is an INT, so only 4 bytes are needed to uniquely identify each row. If you add all remaining 96 bytes per row to the included columns for a non-PK covering index, you're essentially storing the entire 10GB again. Looking up the row data based on the PK only takes 4 bytes per row, instead. Covering indexes cover the QUERY, not the table, and are only useful if your requirements are radically different from the existing (clustered) indexes.
    – adaptr
    Jan 11, 2012 at 11:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .