Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns

Question

I am working on a table design for Customer Totals and trying to make a decision about the primary key. I was going to go with a surrogate identity column with a clustered index, but this column would NEVER be used. The candidate primary key columns are CustomerNumber + AccountNumber, because these are the unique identifiers for each row, but these will NOT be sequentially inserted.

Basically, on a daily basis a report will be run which will update each CustomerNumber + AccountNumber record with the most recent purchase total and total date.

Does it make sense to remove CustomerTotalID completely and have CustomerNumber + AccountNumber be a PK with a NON-clustered index?

    CREATE TABLE CustomerTotals (
    CustomerTotalID INT IDENTITY(1,1),
        CustomerNumber INT,
        AccountNumber INT,
        PurchaseTotal DECIMAL(10,2)
        TotalDate DATE,
      CONSTRAINT [PK_CustomerTotals] PRIMARY CLUSTERED (
    CustomerTotalID ASC
)
    )

Answer 1

If you are going to cluster on something that isn't the primary key then I would ensure that those columns are the most frequently used in predicates. One thing to be aware of though is that if your clustered index is non-unique then SQL Server will have to add a uniquifier and that does not come for free, this link has more detail https://www.mssqltips.com/sqlservertip/2082/understanding-and-examining-the-uniquifier-in-sql-server/ but tl;dr they're 4 bytes and can break if you have too many duplicate combinations.

Personally I like the surrogate identity column for the PK because:

1. It's narrow, so easier on storage
2. It's simpler to post a single column as a foreign key
3. Having a unique value easily visible on the table can help diagnose any possible future data issues.

Point 1 also makes it useful as the clustered index because it can be referenced cheaper than CustomerNumber and AccountNumber.

All that being said, nothing is a substitute for testing all the options you have and seeing which is the best fit for your workload.

Answer 2

Does it make sense to remove CustomerTotalID completely and have CustomerNumber + AccountNumber be a PK with a NON-clustered index?

It does make sense to remove the surrogate key, since you won't be using it.

A primary key on the natural key (CustomerNumber, AccountNumber) should probably be clustered rather than nonclustered.

The query optimizer will introduce a sort for large inserts if it makes sense from a cost point of view. With a nonclustered PK, you'd be inserting to the heap and a separate nonclustered index.

Worrying about fill factors, rebuilding, and fragmentation in general is often overblown. See Why Defragmenting Your Indexes Isn’t Helping.

If new (CustomerNumber, AccountNumber) values are not inserted frequently, then fragmentation and page splitting of the clustered index is a complete non-issue.

If the table is queried by CustomerID alone or CustomerID/AccountNumber, making that the clustered index is a no-brainer.

Answer 3

I'm working on a similar problem right now except more columns and millions of rows. We have a PK that isn't used in any queries. We ran a preliminary test where we changed it to a non-clustered PK and I found two columns that are used in where clauses and created a clustered index on those.

Many queries ran faster and out of more than two dozen indexes on the table we think we will be able to delete 25 of them.

In theory what were are looking to do is not best practices because the columns aren't unique that we're looking to use for the clustered index. But in practice it allows us to save a lot of space by making many non-clustered indexes unneeded and improve I/O by deleting these indexes.