Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

CREATE NONCLUSTERED INDEX [TableA_ColA]
ON [dbo].[TableA] ([ColA])
INCLUDE ([ColB])
GO

CREATE NONCLUSTERED INDEX [TableA_ColB_ColA]
ON [dbo].[TableA] ([ColB],[ColA])
GO

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?

share|improve this question
    
Regarding "do I need both?" impossible for us to tell you as it requires an evaluation of costs and benefits to your overall workload. –  Martin Smith Jun 19 '13 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 '13 at 14:15
add comment

1 Answer 1

up vote 10 down vote accepted

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.

share|improve this answer
    
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? –  Matthew Steeples Jun 20 '13 at 0:42
    
@MatthewSteeples It is only stored on the leaf pages then. Not the upper level pages. –  Martin Smith Jun 20 '13 at 1:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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