From: esquel@sommarskog.se   
      
    (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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)