x

Index With Include Columns

I have run a script that shows SQL suggested indexes which will increase performance greatly. A few of the indexes have the same keys but different INCLUDE columns. Would it be wise to create two indexes or should I combine the INCLUDE columns into one index? What would be the pros/cons of each approach?

 CREATE INDEX [IDX__COLA__COLB] ON [dbo].[TableA] (COLA],[COLB]) INCLUDE ([COLC], [COLD]) on [PRIMARY]
 
 CREATE INDEX [IDX__COLA__COLB] ON [dbo].[TableA] (COLA],[COLB]) INCLUDE ([COLE], [COLF]) on [PRIMARY]
more ▼

asked Jul 31, 2015 at 03:05 PM in Default

avatar image

DBANovice
130 1 1 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Given that the index key is the same, I would combine the two. Include columns are only stored at the leaf level of the index so you minimize the amount of space required so adding in the additional two columns shouldn't be that large of a hit. Plus by combining them, you'll have a single index that will potentially cover more than one query.

I would, however, ensure that the index is indeed being used by the queries.

Hope that helps!

more ▼

answered Jul 31, 2015 at 07:08 PM

avatar image

JohnM
13.7k 3 7 14

Thanks John!

Jul 31, 2015 at 07:57 PM DBANovice

You are welcome!

Aug 01, 2015 at 09:19 PM JohnM

Great answer, John. I wish I could give you a bonus + just for this part: "ensure that the index is indeed being used by the queries". And I'll add that verification of that can often yield different results in dev vs prod, so it helps to check both. Index recommendations on the production instance are far more important than any determined on a development system.

Aug 03, 2015 at 08:38 PM Tom Staab ♦

Thanks @Tom! Excellent point as well, that dev vs prod can be different to test accordingly.

Aug 05, 2015 at 11:48 AM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x410
x156
x148
x81
x11

asked: Jul 31, 2015 at 03:05 PM

Seen: 65 times

Last Updated: Aug 05, 2015 at 11:48 AM

Copyright 2016 Redgate Software. Privacy Policy