Sql-server – Necessary to include clustered index columns in non-clustered indexes

nonclustered-indexsql-server-2005sql-server-2008

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?

Best Answer

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)

Related Solutions

Sql-server – Hard and Fast rule for include columns in index

That index suggestion by marc_s is wrong. I've added a comment. (And it was my answer accepted too!)

The index for this query would be

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Where:

KeyColList = Key columns = used for row restriction and processing
WHERE, JOIN, ORDER BY, GROUP BY etc
NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction

Sql-server – computed columns, index, clustered index and covering index

Computed Columns can be stored in the data page in one of two ways. Either by creating them as PERSISTED or by including them in the clustered index definition.

If they are included in the clustered index definition then even if the columns are not marked as PERSISTED then the values will still be stored in each row. These index key values will additionally be stored in the upper level pages.

If the computed column is imprecise (e.g. float) or not verifiable as deterministic (e.g. CLR functions) then it is a requirement for the column to be marked as PERSISTED in order to be made part of an index key.

So to give an example

CREATE TABLE T
(
A INT,
C1 AS REPLICATE(CHAR(A),100) PERSISTED,
C2 AS REPLICATE(CHAR(A),200),
C3 AS CAST(A AS FLOAT) PERSISTED,
C4 AS CAST(A + 1 AS FLOAT)
)

CREATE UNIQUE CLUSTERED INDEX IX ON T(C2,C3)

C1 will be stored in just the data page rows as it is marked as PERSISTED but not indexed.
C2 will be stored in both the rows on the data page and the index higher levels as it is an index key column.
C3 will be stored as for C2. As it is imprecise it is a requirement to mark it as PERSISTED however.
C4 won't be stored anywhere as it is neither marked as PERSISTED nor indexed.

Similarly all computed columns referenced in non clustered index definitions as key columns need to be stored at all levels of the index as they are part of the index key. There is the same requirement regarding precise/deterministic results.

Fails

CREATE NONCLUSTERED INDEX IX2 ON T(A,C4)

With the error.

Cannot create index or statistics 'IX2' on table 'T' because the computed column 'C4' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

To include it as part of the non clustered index key it must also be stored in the clustered index data pages. However

Succeeds.

CREATE NONCLUSTERED INDEX IX2 ON T(A) INCLUDE (C4)

Computed columns that are only INCLUDEd columns are persisted to the NCI leaf page and do not have the requirement that they also be persisted in the data page.

Best Answer

Related Solutions

Sql-server – Hard and Fast rule for include columns in index

Sql-server – computed columns, index, clustered index and covering index

Related Question