I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database. I added them and the script did indeed run much faster. Now I'm looking at putting these into our standard form for inclusion into our regular upgrade scripts and I noticed what might be a redundancy:

ON [dbo].[TableA] ([ColA])

ON [dbo].[TableA] ([ColB],[ColA])

I can see from the documentation that there are some differences between the two indexes as far as space usage is concerned, but in this case ColA is int and ColB is nvarchar(255), well under the 900 byte limit, so I'm not sure that the INCLUDE in the first index is necessary.

So is this just an arbitrary result of the execution plan analyser? It seems to me if I have the second index the first one is redundant?

  • Regarding "do I need both?" impossible for us to tell you as it requires an evaluation of costs and benefits to your overall workload. Jun 19, 2013 at 13:02
  • @MartinSmith What I meant by 'do I need both' was 'are these two index declarations essentially equivalent', but apparently not :)
    – robertc
    Jun 19, 2013 at 14:15

1 Answer 1


They satisfy different queries.

The first one will be useful for queries that seek on ColA = X and return ColB (without the INCLUDE this would require a lookup back to the base table)

The second one will be useful for queries that seek on ColB = Y

Both of them can be used for queries like ColA = X AND ColB = Y but the second one will do this more efficiently as the first index would require a seek on ColA then the residual predicate be evaluated against all matching index rows.

  • As a follow up to this question, what is the advantage of putting the column in the include section rather than in the index itself? Jun 20, 2013 at 0:42
  • @MatthewSteeples It is only stored on the leaf pages then. Not the upper level pages. Jun 20, 2013 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.