January 11, 2012 at 2:43 am
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 three 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?
2. 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?
January 11, 2012 at 3:20 am
1) http://www.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
2) That will prevent key lookups, but you've just duplicated your entire table, perhaps multiple times. Don't use SELECT *, and keep the includes on indexes to as few columns as possible
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
January 11, 2012 at 3:57 am
GilaMonster (1/11/2012)
1) http://www.sqlservercentral.com/articles/Indexing/68636/http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
2) That will prevent key lookups, but you've just duplicated your entire table, perhaps multiple times. Don't use SELECT *, and keep the includes on indexes to as few columns as possible
Both articles are really good to understand the indexes. Very knowledgeable.
Gail - It would be great if you can add some information about fill factor also in this article (Introduction to indexes series) as this is also a confusing part when it comes to indexes.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply