Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Non-clustered Covering Indexes - Many w/few included columns vs Single w/many included columns Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 9:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:21 AM
Points: 92, 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)?


Post #1433307
Posted Wednesday, March 20, 2013 9:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
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.



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)
Post #1433324
Posted Wednesday, March 20, 2013 11:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 12, 2014 8:21 AM
Points: 92, 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.
Post #1433365
Posted Wednesday, March 20, 2013 12:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 39,952, Visits: 36,308
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 2008, MVP
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

Post #1433399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse