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