When should a primary key be declared non-clustered?

Question

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED

When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key?

Thanks in advance

Answer 1

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'?

And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index certainly do not need to use lookups to satisfy some of the projected columns and/or predicates.

Another piece of the puzzle is how can an index be used? There are three typical patterns:

• probes, when a single key value is seek-ed in the index
• range scans, when a range of key values is retrieved
• order by requirements, when an index can satisfy an order by w/o requiring a stop-and-go sort

So if you analyze your expected load (the queries) and discover that a large number of queries would use a particular index because they use a certain pattern of access that benefits from an index, it makes sense to propose that index as the clustered index.

Yet another factor is that the clustered index key is the lookup key used by all non-clustered indices and therefore a wide clustered index key creates a ripple effect and widens all the non-clustered indices and wide indices mean more pages, more I/O, more memory, less goodness.

A good clustered index is stable, it does not change during the lifetime of the entity, because a change in the clustered index key values means the row has to be deleted and inserted back.

And a good clustered index grows in order not randomly (each newly inserted key value is larger than the preceding value) as to avoid page splits and fragmentation (without messing around with FILLFACTORs).

So now that we know what a good clustered index key is, does the primary key (which is a data modelling logical property) match the requirements? If yes, then the PK should be clustered. If no, then the PK should be non-clustered.

To give an example, consider a sales facts table. Each entry has an ID that is the primary key. But the vast majority of queries ask for data between a date and another date, therefore the best clustered index key would be the sales date, not the ID. Another example of having a different clustered index from the primary key is a very low selectivity key, like a 'category', or a 'state', a key with only very few distinct values. Having a clustered index key with this low selectivity key as the leftmost key, e.g. (state, id), often makes sense because of ranges scans that look for all entries in a particular 'state'.

One last note about the possibility of a non-clustered primary key over a heap (i.e. there is no clustered index at all). This may be a valid scenario, the typical reason is when bulk insert performance is critical, since heaps have significantly better bulk insert throughput when compared to clustered indices.

Answer 2

The basic reason to use Clustered indexes is stated on Wikipedia:

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

Say that I have a table of People, and these people have a Country column and a unique Primary Key. It's a demographics table, so these are the only things I care about; what Country and how many unique people are tied to that country.

I am thus only ever likely to SELECT WHERE or ORDER BY the Country column; a clustered index on the Primary Key doesn't do me any good, I'm not accessing this data by PK, I'm accessing it by this other column. Since I can only have one clustered index on a table, declaring my PK as Clustered would prevent me from using a Clustered Index on Country.

In addition, here's a good article on Clustered vs Nonclustered Indexes, turns out clustered indexes caused insert performance issues in SQL Server 6.5 (which at least hopefully isn't relevant for most of us here).

If you put a clustered index on an IDENTITY column, then all of your inserts will happen on the last page of the table - and that page is locked for the duration of each IDENTITY. No big deal... unless you have 5000 people that all want the last page. Then you have a lot contention for that page

Note that this isn't the case in later versions.

Answer 3

If your primary key is of the UNIQUEIDENTIFIER, make sure to specify that it's NONCLUSTERED. If you make it clustered, every insert will have to do a bunch of shuffling of records to insert the new row in the correct position. This will tank performance.

Answer 4

A very common example:

• Customer table with CustomerID as CLUSTERED PRIMARY KEY
• Order table with OrderID (PK), CustomerID, OrderDate and some other columns
• OrderPositions with OrderPositionID (PK), OrderId, ProductID, Amount, Price ...
• you have to index the Order tables

Of course "it depends" is - as nearly always - the correct answer, but the most applications (not BI-Reports) will work customer based (e.g. you log in as customer 278 into the website and click onto the "My orders" or the clerk lists all orders for customer 4569 or your invoice routine will summize all orders for customer 137).

In this case it would make not much sense to cluster the table by the OrderID. Yes, you will have queries as SELECT ... WHERE OrderId = ? to list the order details, but this would be usually short and cheap (3 reads) index seeks.

On the other hand, if you would cluster your Order table by the CustomerID, it would not have to do multiple key lookups every time you query the table for CustomerId = ?.

The CLUSTERED INDEX should be always UNIQUE, otherwise SQL Server would add an invisible (= unusable) INT column UNIQUIFIER to ensure the uniquiness - and it would make much more sense to add real (usable) data then some random (depending on the inserting order) stuff.

Because a customer will (hopefully) place more than one order, we would have to add either the OrderID or (if you usually sort for this) the OrderDate (if it is a datetime - otherwise the customer would be limited to one order per day) to the CLUSTERED INDEX and end up with:

CREATE UNIQUE CLUSTERED INDEX IX_Orders_UQ on Orders (CustomerID, OrderID)

The same rules applies to the OrderPositions table. Usually the most queries will list all positions for on specific order, so you should create the PK with the OrderPositionID as NONCLUSTERED and a UNIQUE CLUSTERED INDEX on OrderId, OrderPositionID.

BTW: it is correct that the Customer table is clustered by its PK (the CustomerID, because it is a "Top-Level-Table" and will - in a typical application - mostly be queried by its CustomerID.

Pure lookup Tables as e.g. Genders or InvoiceTypes or PaymentType are another example of tables that should be clustered by its PK (because you'll usually join them on GenderId, InvoiceTypeId or PaymentTypeId).

Answer 5

When a clustered index is deemed to be more beneficial to the overall system than a clustered PK by using some measure of performance. There can only be one clustered index on a table.

Example measures of performance are single query time (speed), integration of total query times against the table (efficiency) and having to add many include columns to a to a very large non-clustered index in order to achieve performance similar to clustered (size).

This can happen when data is generally retrieved using an index that is not unique, contains nulls (not allowed in a PK), or the PK was added for a secondary reason (such as replication or audit trail record identification).

Answer 6

In my experience it is better for long-term, overall performance to make your PK also your clustered key for a table, especially when you get up in the million-row plus range. But as it has been said above, a very important characteristic of such a key is that it is always increasing as records are inserted.

Said another way, a use case for a non-clustered PK would be if the PK is on a column that is NOT increasing with new records, because the engine will have to pick up and move existing records to maintain their ordinal position on disk, potentially magnifying the I/O required for those inserts by many orders of magnitude.

If anyone has ever seen a SQL representation of "Id" fields coming from Salesforce objects, this illustrates that scenario exactly. The values are a kind of miniature GUID and are always unique, but definitely not ascending.