StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONon Clustered Covering Index Column Selection Rules
text
Body
copied!<p>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:</p> <ol> <li><p>let us assume that we have the following SPs which queries as:</p> <pre><code>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} </code></pre> <p>I have created the following Non Clustered indexes on the table as</p> <p>[col_a] and [col_b] --> Would the first SP still use this index as the orders are reversed</p> <p>[col_a] and [col_c] and [col_d] --> Would the last SP use this index as the first two columns match with order</p> <p>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?</p></li> <li><p>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? </p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload