Sql-server – the difference between these two index declarations and do I need both

indexindex-tuningsql server

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?

Best Answer

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.

Related Question