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 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)

share|improve this question
2  
Are Index1, Index2, and Index3 nonclustered indexes? Is this table a clustered index with it on Id? –  Thomas Stringer Dec 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 Mijelshon Dec 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 Stringer Dec 7 '12 at 15:06
    
Scratch that; the PK IS clustered. I was looking at the wrong index. Updated. –  Diego Mijelshon Dec 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. –  Zane Dec 7 '12 at 15:42
add comment

3 Answers 3

up vote 7 down vote accepted

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;

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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 White Dec 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... –  a1ex07 Dec 7 '12 at 17:40
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.