Sql-server – the effect on primary keys when moving a CLUSTERED index to a new filegroup

You are correct to separate "clustered index" from "primary key":

• A clustered index is the organisation of data on disk is better if
  • narrow
  • numeric
  • increasing (strictly monotonic)
• The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

• ProductHistoryID
• ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

• clustered index should be on ProductHistoryID
• unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

or

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

A clustered index will dictate the order of the data in the pages of your table. When your temp table is initially populated, the inserted data will be sorted or, if you're doing multiple inserts to populate, it's possible you will end up with some page fragmentation as the pages must split to accommodate additional data being written.

Assuming your temp table is populated in a single insert, and you have no page fragmentation, the next issue that may or may not affect your performance is the presence of a 4 byte "uniqueifier." Because the clustered key is used as a pointer to each row in the table, it must be unique for that purpose. So if you're using a non-unique clustered index, every nonclustered index (and your clustered index) is going to be 4 bytes wider than you've defined which will affect page density and IO required to perform the query. This will likely only be of very marginal impact as the uniquifier will only be added to duplicates.

The other factor that you need to consider is, how are you joining to this temp table. SQL Server supports 3 physical join operators; Merge, Loop, and Hash. Merge can yield the best performance in many situations.

Unlike the nested loops join where the total cost may be proportional to the product of the number of rows in the input tables, with a merge join each table is read at most once and the total cost is proportional to the sum of the number of rows in the inputs. Thus, merge join is often a better choice for larger inputs.

It is unlikely SQL will chose a merge operator unless both datasets being joined are sorted by the join criteria. There are other factors as well, of course, but if you can optimize your table for specific, common usage patters, all the better.

The second thing to consider is any other indexes you are going to place on that table since each nonclustered index will contain the keys specified (in the order specified) and the pointer to the clustered index to facilitate the lookup and retrieval of any fields not covered by the index. Again, a narrow clustered key will yield better density in your non-clustered indexes and thus better i/o performance for index scans and seeks. If your nonclustered indexes require many lookups to the clustered index either consider including those columns in your index (sacrificing page density for reduced lookups) or consider clustering on that criteria (if this is your primary usage pattern)

The biggest factor that would steer me towards using a primary key over a unique (or non-unique clustered index) would be the ability to create the index inline in the create table statement. Historically, I have found that the optimizer is less apt to use indexes that I created inside my stored procedure after the create table statement. It's easier to create a primary key inline with the create table statement. I'm not sure if it's possible to do this with a non-constraining index but you can create a unique clustered index inline.