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

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

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

Show key headers only | View raw

 (rja.carnegie@gmail.com) writes:
> 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?

Depending on the queries it is useful or useless. That is, one cannot
say anything a priori.

Say that you have a query like:

   SELECT *
   FROM   tbl 
   WHERE  SeemisSubjectID = @somesubject
     AND  costcentre LIKE 'AB%'

With only SeemisSubjectID in the index, all umpteen rows with matching 
SeemisSubjectID has to be scanned for matching costcentre. With 
costcentre as part of the index key, the optimizer can seek all the way.
   
 
> 2. If instead I am considering the following nonclustered index,
> is [costcentre] redundant?
> 
> CREATE NONCLUSTERED INDEX IX13_Ali_SeemisSubjectID ON AliasSubject
> (SeemisSubjectID) INCLUDE (costcentre)
> ;

Yes, it is redudant, but it is not malign. Since costcentre is part 
of the clustered key it is included in the index anyway. However, you 
may decide to change the clustered index or make the table a heap. By 
explicitly including costcentre in the index, you ensure that the 
index will remain covering for the query you had in mind.
 
> 3. Are the answers different if [SeemisSubjectID] is a unique key
> but I also want to use [costcentre] in a JOIN condition?
 
Yes, if SeemisSubjectID is unique, then adding costcentre as in index 
key is redudant and you save some space on the upper levels of the
index by leaving it out, or only having it as an included column.
-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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