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

sql servertable

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

Best Answer

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.

Related Question