Determining if indexes are redundant I have a table with the following structure:

Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields


The following indexes have been defined:

PK (Id), clustered
Index1 (FooId)
Index2 (FooId, BarId)
Index3 (FooId, BarId, Id, QuxId)


Is it redundant to keep both Index1 and Index2?

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

Would it make more sense to have just one index, (FooId, BarId, QuxId)? Would there be any benefit in including Id?

(as a sidenote, I'm a developer, not a DBA)
Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?


It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id).  Take a look at the following example:

use TestDB;
go

create table FooBar
(
    Id int identity(1, 1) not null
        constraint PK_FooBar_Id primary key clustered,
    FooId int not null,
    BarId int not null,
    QuxId int not null
);
go

create index Index1
on FooBar(FooId);
go

create index Index2
on FooBar(FooId, BarId);
go

create index Index3
on FooBar(FooId, BarId, QuxId);
go

insert into FooBar(FooId, BarId, QuxId)
values
    (1, 2, 3),
    (2, 3, 4),
    (4, 5, 6),
    (5, 6, 7),
    (6, 7, 8),
    (7, 8, 9);
go


select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar
where FooId = 4;




By capturing the execution plan for the last query (the SELECT), you can see that it is an index seek on [FooBar].[Index3], and notice my CREATE INDEX doesn't include the Id column.

Martin brings up a good point in his comment (see below for quote):


  But specifying Id in a particular position in the index rather than just accepting the defaults can avoid a sort if the requirement is to ORDER BY that column order. Also allows a straight forward lookup on FooId, BarId, Id which an index on FooId, BarId, QuxId, Id wouldn't do (whether or not Id is included in the NCI key or leaf depends if the index is declared as unique or not)


Here is what he's talking about.  With a query like this (forced index table hint to show the Sort operation):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with (index(Index3))
where FooId = 4
order by Id;


This would generate the below plan:



But by changing the structure of the index, like so:

create index Index3
on FooBar(FooId, Id, BarId, QuxId)
with (drop_existing = on);
go


And re-executing the above query:

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar -- with (index(Index3))
where FooId = 4
order by Id;


You now don't have the expensive Sort operation:



Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;


You will again have that Sort operation.  Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go


By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId.  Here is proof with that altered index:



To get a visual of that, let's look at the leaf page of Index3.  You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go


You will get similar output like the following: