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]
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!
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.
16 People are following this question.
