CODEWITHSUNDEEP
sqlsql-serverdatabase
balaji
balaji

Reputation: 1304

SQL Server - Any advantage of using composite primary key here?

Below is my table

Rate table:

rate_id   start_date   end_date     rate1   rate2  rate3
---------------------------------------------------------
  1       2014-08-24   2014-08-28     50     40      30
  4       2014-08-28   2014-08-23     45     36      46
  1       2014-08-29   2014-08-31     60     45      66

rate_id is a foreign key.

Most of my search queries on this table looks like below

select * 
from Rate 
where rate_id = 1 
  and start_date between 'xxx' and 'yyy'

Combination of rate_id and start_date is unique.

  1. Can I create a composite primary key on (rate_id, start_date)? And is it useful?
  2. or do I need to create a nonclustered index on 3 columns?

Upvotes: 1

Views: 755

Answers (2)

Jitendra Rathor
Jitendra Rathor

Reputation: 130

Choice of your primary key should not depend on your workload.primary key is a tuple which makes a row unique in rowset.

And as far as performance optimization is concerned, it depends on your query workload.IF you are using rate_id & start_date in most of the queries.Then it makes sense to use both columns in index. otherwise you should have only rate_id.

In your case your filter predicate uses both rate_id & start_date in most of the queries so you should have rate_id+start_date as composite primary key.It will save you index stoarage space and will have less update/insert cost. Because sql will have to update only one index(composite key) instead of 2 indexs.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453028

Yes a composite clustered primary key on those two columns in that order would be very good for that query.

It would allow a simple range seek to be used on a covering index. To be declared as a primary key the columns must both not be nullable and the combination must be unique but I assume this is the case.

The rate1, rate2, rate3 looks suspect though and may indicate your table is not in first normal form.

Upvotes: 2

Related Questions