Can a primary key column be a part of clustered index and unique non clustered index ?

Question

Hi,

Question-Can a primary key column a part of clustered index and unique non clustered index ? Is it ok ?

Context-We are validating the primary key within our database are up to the standards.

Answer 1

Yes. A primary key is always implemented as an index(*), so a primary key column must always be part of at least one index. And, by default, when you define a primary key, this will be the clustered index, even though this is far from always the best choice for the clustered index. And, as needed, you can add the key columns to other indexes as well.

(*) Well, for memory-optimized tables, it can also be implemented with a hash table.

Answer 2

Can a primary key column a part of clustered index and unique non clustered index ?

Yes, you can have the same column as a primary key column as well as a unique constraint or index key column (unique or not). The choice as to which index is clustered is a separate decision.

Is it ok ?

Like nearly all things SQL Server, "it depends". You didn't mention your standards so I'll call out a few considerations.

Having a clustered and non-clustered unique constraint/index with the same key columns and in the same order is unusual. It could be appropriate in a specialized workload where queries rarely need non-key columns. In that case, the non-clustered index improves buffer efficiency by avoiding reading the data page with unneeded data into memory.

The primary key is often the best choice for the clustered index, which is why SQL Server creates the PK index as clustered by default unless a clustered index already exists on the table. However, depending on your queries and workload, a clustered index other than the primary key might be more appropriate.

Consider the clustered index key columns are implicitly included in non-clustered index leaf nodes as the row locator unless already an index key or included column. This can help cover queries but increase storage/memory needs with wide clustered index keys.

Answer 3

Hi @Kashish

You can refer to this official document for more information.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16

Best regards,

Percy Tang