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 ) )