Include additional columns in a non clustered primary key index

Question

I have defined the primary key on a table as nonclustered. With non-clustered indexes which are explicitly created by create nonclustered index it is possible to include additional (non-indexed) columns. Is the same also possible for the implicitly created primary key non-clustered index?

Answer 1

Although one cannot specify included columns with a primary key or unique constraint, a workaround is a unique non-clustered index with the same key along with included columns. Not only does this provide the same uniqueness guarantee as a primary key constraint, foreign keys can reference a unique constraint/index key too, providing the same referential integrity as a referenced primary key.

The upside with this method is performance for specialized use cases, such as when the best clustered index choice is not the primary key and included columns are desirable for performance.

Downsides are one can only specify declarative cascading DRI with a primary key constraint and not intuitive.

Answer 2

The syntax for INCLUDE columns is only available for CREATE NONCLUSTERED INDEX, specifically

INCLUDE (column [ ,... n ] ) Specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.

Is it not available for ADD CONSTRAINT, so you cannot INCLUDE any columns with a Primary Key, even if it is non-clustered.

A PRIMARY KEY is useful as a UNIQUE identifier for the record, and is a candidate key available for REFERENTIAL constraints. However, for performance reasons, if you have a clustering key on another column and you have no other candidate keys to promote as a PK, you can always create an additional non-clustered index on the primary key column(s) and INCLUDE other columns onto the index.