SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns


Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns

Author
Message
sixthzenz
sixthzenz
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 348
I have several non-clustered covering indexes on a table that have the same key columns with few included columns. I'm wondering if i can just create one NC covering index with all the included columns from each of the existing indexes.

For example (K = key column, IC = included column),
IDX 1 = K1,K2 + IC1,IC2
IDX 2 = K1,K2 + IC3,IC4
IDX 3 = K1,K2 + IC5,IC6
IDX 4 = K1,K2 + IC4,IC6

If I remove these and make one index (e.g., IDX = K1,K2 + IC1,IC2,IC3,IC4,IC5,IC5), will the queries be just as efficent? Or does it depend on the situation (i.e., query execution frequency)?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91501 Visits: 38950
sixthzenz (3/20/2013)
I have several non-clustered covering indexes on a table that have the same key columns with few included columns. I'm wondering if i can just create one NC covering index with all the included columns from each of the existing indexes.

For example (K = key column, IC = included column),
IDX 1 = K1,K2 + IC1,IC2
IDX 2 = K1,K2 + IC3,IC4
IDX 3 = K1,K2 + IC5,IC6
IDX 4 = K1,K2 + IC4,IC6

If I remove these and make one index (e.g., IDX = K1,K2 + IC1,IC2,IC3,IC4,IC5,IC5), will the queries be just as efficent? Or does it depend on the situation (i.e., query execution frequency)?




I would consolidate the indexes. This will reduce the space used as 1) you will not be duplicating the two key columns + the clustered index key and 2) the included columns won't be duplicated either as it appears a couple of them are.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sixthzenz
sixthzenz
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 348
Thanks, that was my initial instinct/thought but just wanted someone to confirm it. My reasoning, which I just learned, was based on the fact that the order of the included columns don't matter. If that's the case, it'd only make sense to have them all combined.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215664 Visits: 46270
Yup, consolidate, that should be one index.

The only counter case for keeping it separate would be where queries that use those indexes absolutely must run as fast as possible and not a microsecond more, in that limited case you would probably want multiple indexes for the fastest access possible (larger indexes = more data).

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search