2

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one.

To me, this seems the same as just having a Primary Key, and an extra UNIQUE key.

So I have two questions:

  1. if a Primary Key is non-clustered, does it store all the columns with it? Or only the Primary Key columns and the columns referencing the clustered index?

  2. I've just read that if the PK isn't the clustered index, then the clustered index does NOT have to be UNIQUE (but it's highly encouraged). Does this mean then that the table could be "randomly sorted" on the rows with the same key?

2 Answers 2

4

if a Primary Key is non-clustered, does it store all the columns with it?

Nope, that is a characteristic of the clustered index, not the primary key. A nonclustered primary key will only store the fields it is defined on plus the clustered index's key fields.

...the clustered index does NOT have to be UNIQUE (but it's highly encouraged). Does this mean then that the table could be "randomly sorted" on the rows with the same key?

The rows of the table will always be logically sorted by the clustered index fields. In the case where the clustered index is not unique, and there are two rows with the same values for the clustered index key, those rows are given a unique row identifier that is stored behind the scenes (this 4 byte uniquifier is only added to the duplicate key rows). This is the determining factor in how they're sorted relative to each other.

One reason why one might choose to make the primary key a nonclustered index is because they find performance benefits in having the data logically sorted differently than the fields that identify uniqueness for the row (regardless if the clustered index is unique or not).

An example would be if a GUID was used in a UNIQUEIDENTIFIER column as the primary key of your table. GUIDs are great for uniqueness (most times) but aren't usually a great way to keep your data sorted because of their random-like values. Instead you may have a natural set of fields in that table, that your queries typically join or filter on, that aren't necessarily guaranteed to be unique, which instead make for a good clustered index then. Or even a set of fields you typically order on in your queries could be a candidate for a clustered index, to eliminate a heavy sort operation from the query plan. The data will then be sorted in an order that makes sense instead of by the semi-random values of a GUID.

Please see SQL Server Clustered Indexes internals with examples for more information on how clustered indexes work.

4
  • Thank you very much. So basically, the Primary Key is simply a UNIQUE secondary key in those scenarios. There doesn't seem to be any benefit in ever making the PK a non-clustered key, except if I wanted the clustered index not to be unique, but that's not even recommended anyway, and will just make things confusing...
    – Nuno
    May 16, 2022 at 11:54
  • 1
    @Nuno No problem! Please see my updated answer with an example when you may choose to make the primary key nonclustered and a different set of fields the clustered index.
    – J.D.
    May 16, 2022 at 12:02
  • 1
    Ah ok! That's actually a good example/suggestion, yes. Thank you for that. I suppose one could create a separate index and sort ASC/DESC the way they want, but it would likely not be a covering index, so making it clustered could increase performance. Cheers! Good day to you.
    – Nuno
    May 16, 2022 at 12:15
  • Personally, I've found non-clustered primary keys far more common than clustered ones. A use-case I came across a few years ago was having a clustered index on the postcode. The Post Office gave a significant discount on postage if the bulk mail was sorted by postcode, so almost every query had ORDER BY postcode in it. May 16, 2022 at 22:59
1

if a Primary Key is non-clustered, does it store all the columns with it? Or only the Primary Key columns and the columns referencing the clustered index?

The index type, clustered or non-clustered, implictly determines the non-key columns stored in the b-tree index leaf nodes. This is true regardless of whether the index is unique or not, or supports a primary key or unique constraint.

A clustered index organizes the table itself as a b-tree index (in contrast with a heap when no clustered index exists). All columns are stored in the leaf nodes of a clustered index because those are the actual data pages/rows. The unique row locator is the clustered index key plus an internal uniqueifier for non-unique key values when applicable.

A non-clustered index stores the non-clustered index key columns, row-locator (clustered index key), plus included columns (if specified) in the index leaf nodes.

I've just read that if the PK isn't the clustered index, then the clustered index does NOT have to be UNIQUE (but it's highly encouraged). Does this mean then that the table could be "randomly sorted" on the rows with the same key?

A table is logically ordered by the clustered index key (unique or not). Rows with the same clustered index key will be adjacent, not random.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.