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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL Server 2012 - T-SQL » Non-clustered Covering Indexes - Many w/few...

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

Rate Topic

Display Mode

Topic Options

Author

Message

sixthzenz

Posted Wednesday, March 20, 2013 9:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:49 PM
Points: 92, Visits: 341

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

Lynn Pettis

Posted Wednesday, March 20, 2013 9:57 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 21,592, Visits: 27,402

sixthzenz (3/20/2013)

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)

Post #1433324

sixthzenz

Posted Wednesday, March 20, 2013 11:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:49 PM
Points: 92, Visits: 341

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

GilaMonster

Posted Wednesday, March 20, 2013 12:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665, Visits: 29,918

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 »

Permissions