Sample Header Ad - 728x90

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

1 vote
3 answers
470 views
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 ) )
Asked by user2966445 (141 rep)
Jul 26, 2018, 04:04 PM
Last activity: Feb 7, 2022, 10:15 AM