Necessary to include clustered index columns in non-clustered indexes?

Question

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?

Answer 1

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)