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

Rahul 251 Reputation points
2023-10-15T07:36:21.4966667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-15T09:47:45.1466667+00:00

    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.

    0 comments No comments

  2. Dan Guzman 9,211 Reputation points
    2023-10-15T10:42:58.2933333+00:00

    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.

    0 comments No comments

  3. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-10-16T09:33:58.1033333+00:00
    0 comments No comments