0

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.

Improve this question
1

3 Answers 3

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I agree that it is redundant in terms of enforcing uniqueness and referential integrity. However, having a single value to identify a unique row can make certain complex queries simpler. I'd say (and this is deliberately redundant) the surrogate key is only required if you have a query where it's required.
Even if you don't need it now - having a surrogate key makes it so much easier to use an ORM at some point in the future.
@BonyT: ORMs are broken. See here for a detailed exposition of why I say this.
@Dems: It would only make queries simpler if some other relation had a foreign key on the surrogate. Since evidently nothing currently has a foreign on the existing candidate key, this wouldn't be the case.
@Marcelo - I disagree - quite strongly with your statement, but I suspect we share common sentiments. Having worked without an ORM, with home baked ORM's, and with NHibernate then I know which I prefer. However, bolting on an ORM to a schema design badly designed for it is just plain stupid. Like anything else in the software industry ORM is a tool, and any tool can be misused.
|
1

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.

Improve this answer

1 Comment

"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." What does that mean? That without a surrogate ID number, rows are in danger of flying apart?
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.