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.

Are Index1, Index2, and Index3 nonclustered indexes? Is this table a clustered index with it on Id?
–
Thomas StringerDec 7 '12 at 14:58

They are all non-clustered. There's no clustered index defined; the PK should probably have been (it's identity)
–
Diego MijelshonDec 7 '12 at 15:00

1

So when the primary key constraint was specified it was explicitly set as a nonclustered index? The reason I ask is, because by default a primary key constraint creates a clustered index provided there isn't one on the table already and unless otherwise specified.
–
Thomas StringerDec 7 '12 at 15:06

Scratch that; the PK IS clustered. I was looking at the wrong index. Updated.
–
Diego MijelshonDec 7 '12 at 15:16

You can always take a peek and index usage stats and if the server is not making any use of one of them take a look at dropping it that way.
–
ZaneDec 7 '12 at 15:42

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 INDEXdoesn'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;

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:

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

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3c (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
AND BarId = 7
AND QuxID BETWEEN 123 AND 314 ;
SELECT QuxId
FROM table
WHERE FooId = 5
AND BarId = 7
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.

There is no point including leading column of clustered index into non-clustered index; non-clustered indexes contain clustered key unless your table is heap.

Having 3 different indexes with the same leading column[s] is redundant - even though the engine in some cases may decide use to different indexes (say you are are querying WHERE FooId = 1, so Index1 may be the best choice because the index itself is smaller then Index2 or Index3 in terms of IO operations , but I doubt it can be a huge benefit compared to overhead needed to maintain 3 indexes). You need to decide what are the most frequent queries and create an index for their max benefit. Also, in SQLServer you can INCLUDE column[s] in the index (http://msdn.microsoft.com/en-us/library/ms190806.aspx) , thus reducing overhead in maintaining non-leading columns.

Thomas Stringer's answer shows why the first statement in this answer is incorrect. Also consider what would happen to the nonclustered index structures if the clustered index were to be changed in future.
–
Paul WhiteDec 7 '12 at 17:08

@SQL Kiwi : I agree that the first statement doesn't cover all the cases, and Thomas Stringer's answer shows when it's not true (however, 1. no subtree cost included, so it's not very clear how "expensive" is sort operation in this particular case compared to maintenance expenses ; 2. id is his index is on different position than in OP's question). As to changes to clustered index I think anyone who is going to do so should know what he is doing...
–
a1ex07Dec 7 '12 at 17:40