Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1518

Is a column redundant in a nonclustered index if it's in the clustered index?

Show key headers only | View raw

SQL Server 2005, and the question may not make sense.  Or the answer
may be "It doesn't matter very much which you choose".  But, here goes.

I'm considering using index statements such as,

ALTER TABLE     AliasSubject
ADD CONSTRAINT  PK_AliasSubject
PRIMARY KEY CLUSTERED (costcentre, AliasSubjectID)
;

CREATE NONCLUSTERED INDEX IX13_Ali_SeemisSubjectID ON AliasSubject
(SeemisSubjectID, costcentre)
;

costcentre is char(7), the other columns are char(2), the 
table row size is around 100 bytes, and the table has a few 
thousand rows.  [IX13_Ali_SeemisSubjectID] doesn't have
a unique key.

I think I understand that the nonclustered index is based on
the clustered index, so in a sense the clustered index key 
is there in the index.

I am rewriting a design in which the previous version of 
[IX13_Ali_SeemisSubjectID] has only [SeemisSubjectID] as key,
but I suspect that queries are going to involve both [costcentre]
and SeemisSubjectID] in a JOIN condition.

But I'm wondering:

1. Given that [costcentre] is the first term in the primary key 
and the clustered index, is it redundant, not useful, to tack on
[costcentre] at the end of  the non-unique key in the 
nonclustered index?

2. If instead I am considering the following nonclustered index,
is [costcentre] redundant?

CREATE NONCLUSTERED INDEX IX13_Ali_SeemisSubjectID ON AliasSubject
(SeemisSubjectID) INCLUDE (costcentre)
;

(This is very like the first nonclustered index with a shorter key,
but I think that a non-unique index internally uses a key 8 bytes 
longer that is unique, in which case it's a choice between 
10 bytes or 17, not 2 bytes or 9.)

3. Are the answers different if [SeemisSubjectID] is a unique key
but I also want to use [costcentre] in a JOIN condition?

I suppose there is also an argument to put the column in even if
it's redundant for query performance and makes the key longer and
less efficient, so that somebody coming to it after me can see 
that the column is considered important.  But I'm already
choosing to disbelieve the version that I found, that seems to
imply that the column isn't important.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Is a column redundant in a nonclustered index if it's in the clustered index? rja.carnegie@gmail.com - 2013-06-11 07:03 -0700
  Re: Is a column redundant in a nonclustered index if it's in the clustered index? Erland Sommarskog <esquel@sommarskog.se> - 2013-06-11 20:53 +0200
    Re: Is a column redundant in a nonclustered index if it's in the clustered index? rja.carnegie@gmail.com - 2013-06-17 09:24 -0700

csiph-web