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,
    CustomerTotalID ASC
  • Why non-clustered? Why not make it a clustered pk? It is the natural key for the table, and an artificial PK or clustered index would seem to be redundant. Just give it a fillfactor that allows for inserts and remember to defrag the index occasionally. – Laughing Vergil Jul 26 '18 at 16:15
  • No performance concern with not inserting sequentially and 10 million + rows? – user2966445 Jul 26 '18 at 16:16
  • With a nonclustered PK, you'd have to insert to the heap and a nonclustered index. – Erik Darling Jul 26 '18 at 16:27
  • @sp_BlitzErik Is that worse than having a fragmented clustered index? – user2966445 Jul 26 '18 at 16:28
  • 1
    I generally don't care about fragmentation. – Erik Darling Jul 26 '18 at 16:30

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.

| improve this answer | |

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.

| improve this answer | |
  • Thanks. I guess the concern is if the surrogate identity column is never used (and never will be used as a FK), then what's the point in having it? – user2966445 Jul 26 '18 at 21:08
  • Points 1 and 3 to be honest. If it's an OLTP system then having the identity as the clustered index ensures new records will go in with the least hassle. You can always stick a non-clustered index with includes (covering) to make it really quick to search by CustomerNumber and AccountNumber. You could even consider a non-clustered columnstore if you're running OLAP work loads directly on it. – CasualFisher Jul 26 '18 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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