CODEWITHSUNDEEP
sqlsql-server-2008composite-primary-key
MarcoF
MarcoF

Reputation: 205

Composite Index primary key vs Unique auto increment primary key

We have a transaction table of over 111m rows that has a clustered composite primary key of...

RevenueCentreID  int
DateOfSale       smalldatetime
SaleItemID       int
SaleTypeID       int

...in a SQL 2008 R2 database.

We are going to be truncating and refilling the table soon for an archiving project, so the opportunity to get the indexes right will be once the table has been truncated. Would it be better to keep the composite primary key or should we move to a unique auto increment primary key?

Most searches on the table are done using the DateOfSale and RevenueCentreID columns. We also often join to the SaleItemID column. We hardly ever use the SaleType column, in fact it is only included in the primary key for uniqueness. We dont care about how long it takes to insert & delete new sales figures(done over night) but rather the speed of returning reports.

Upvotes: 0

Views: 1645

Answers (3)

BonyT
BonyT

Reputation: 10940

If you don't care about the speed of inserts and deletions, then you probably want multiple indexes which target the queries precisely.

You could create an auto increment primary key as you suggest, but also create indexes as required to cover the reporting queries. Create a unique constraint on the columns you currently have in the key to enforce uniqueness.

Index tuning wizard will help with defining the optimum set of indexes, but it's better to create your own.

Rule of thumb - you can define columns to index, and also "include" columns.

If your report has an OrderBy or a Where clause on a column then you need the index to be defined against these. Any other fields returned in the select should be included columns.

Upvotes: 0

Marcelo Cantos
Marcelo Cantos

Reputation: 185842

A surrogate key serves no purpose here. I suggest a clustered primary key on the columns as listed, and an index on SaleItemID.

Upvotes: 1

KenL
KenL

Reputation: 875

In have learned you want and need both a natural key and a surrogate key.

The natural key keeps the business keys unique and is prefect for indexing. where the surrogate key will help with queries and development.

So in your case a surrogate auto incrementing key is good in the fact it will help keep all the rows of data in tact. And a natural key of DateOfSale, RevenueID and maybe ClientID would make a great way of ensuring no duplicate records are being stored and speed up querying because you can index the natural key.

Upvotes: 1

Related Questions