question

DBANovice avatar image
DBANovice asked

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]
tsqlssmsindexingindexsql2014
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
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!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DBANovice avatar image DBANovice commented ·
Thanks John!
0 Likes 0 ·
JohnM avatar image JohnM commented ·
You are welcome!
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
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.
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Thanks @Tom! Excellent point as well, that dev vs prod can be different to test accordingly.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.