Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index?

In other words if the Products table contains a clustered index on ProductID, when creating a non-clustered index where it would be advisable to include the ProductID column, is it necessary to nonetheless add it as a column?

If not, are there scenarios where it would be good to add the column name to the non-clustered index?

share|improve this question
2  
Which DBMS are you using? You might also be interested in this: use-the-index-luke.com/blog/2014-01/… –  a_horse_with_no_name Jan 24 at 11:46
    
MSSQL 2005/8R2. Thanks for the reference. –  Nico Jan 24 at 14:02

1 Answer 1

up vote 10 down vote accepted

In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys).

For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key.

You might want to add the columns in explicitly if the default placement is not optimum for your queries. For example if you want to control the ASC/DESC direction or you want to control the position of the key columns in the index.

CREATE TABLE T
(
A INT,
B INT,
C INT ,
PRIMARY KEY CLUSTERED (B DESC, C DESC)
)

/*Implicitly adds B DESC, C DESC to end of key*/
CREATE NONCLUSTERED INDEX ix1 ON T(A ASC) 

/*No sort operation*/
SELECT  *
FROM T
ORDER BY A ASC,B DESC, C DESC

/*
But the avove index won't be able to seek into A,C  
and will need a residual predicate after seeking into A.

For the following query
*/

SELECT  *
FROM T
WHERE A=1 AND C > 4
ORDER BY C ASC, B DESC

/*This index explicitly controlling the key column position 
  and direction would be better*/
CREATE NONCLUSTERED INDEX ix2 ON T(A ASC, C ASC, B DESC) 
share|improve this answer
    
To clarify, if the clustered index is non-unique then the CI key + uniquifier is INCLUDEd if the NCI is unique, otherwise they're both put as part of the NCI key? Same sort of thing if the table is a heap? –  Jon Seigel Jan 24 at 15:11
    
@JonSeigel - Yes. SQL Server always ends up making every index's key columns unique. So if the CI is not declared as unique then the uniqueifier (if present) just gets treated the same as any other CI key column when copied into the NCI. –  Martin Smith Jan 24 at 15:17
    
That makes sense. Thank you. –  Jon Seigel Jan 24 at 15:35
    
Martin & Jon, thousand thanks for your comments. –  Nico Jan 27 at 8:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.