SQL Server Performance

advantage of duplicating Clustered key in non clustered index..

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Aston, Mar 31, 2008.

  1. Aston New Member

    Hi,
    I have a table havnig clustured index in indentity column C1.
    If i make another non clustered index COMBINED on onther columns : C4,C3,C6,C1,C8 . This is again using C1 again.
    My Question is:
    1) How fruitful non clustered index would be as it is having clustered index key again in this ? because when ever we use these columns (C4,C3,C6,C1,C8) in the where clause. Optimizer should always go for clustered index not for non clustered index.
    Any thoughts on this !!! what type of query can use above non clustered index ???
    Thanks
  2. Adriaan New Member

    The main purpose of the clustered index is to serve as the foundation for the other indexes. Ideally, a clustered index is on a unique column with a steadily increasing value (like an identity column) meaning that new rows will be added at the end of the index, and never have to be squeezed in. This acts like a shortcut to the row, which is then used by nonclustered indexes.
    Think of having to look up a person's telephone number in a phone book that is organised by (1) house number, (2) full name, (3) street. Look at a real phone book: first the town, then the family name, then the first name, then the street. In database form, you can picture the Social Security Number as the clustered index.
    There is no point in adding a unique column to a multi-column nonclustered index, neither as the first nor as any of the other columns. Not as the first column, because this should be a non-clustered index that is already built on top of the clustered index on the unique column. And not as any of the other columns, because the value is different on each row, so it doesn't add any ordering. Even worse: if this nonclustered should be unique, then the inclusion of a unique column means that you can have duplicates on all the other columns, but the entry on the index will still be unique.
  3. ScottPletcher New Member

    The clus key column(s) will always be added by SQL to every non-clus index, so there is no reason to add it yourself.

Share This Page