Composite Primary Key

Question

Composite Primary Key

SriSun

Say Hey Kid

Points: 684
More actions
August 11, 2011 at 8:28 am

#255380

Hi,
I have a table with 30 millions rows and trying to create a composite primary key (date_col, idno).
I query data using date_col.
My question: Is it a good practice to use composite primary key, rather I can create primary key on date_col and create nonclustered on idno.
Any suggestions please....
Thanks,
Sri.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sri

I think you may be confusing primary key and clustered index here. When choosing a primary key, if one column uniquely identifies rows in the table, choose that. If you need a second column to make it unique, use a composite. If, on the other hand, you're choosing which column(s) to put your clustered index on, you need to do what works best for your data and the way you insert, modify and read it. I don't think we have enough information to make that judgement for you. All other things being equal (which they rarely are), you want to keep your clustered index as narrow as possible for optimum performance.

John

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

SriSun (8/11/2011)
.. Is it a good practice to use composite primary key, rather I can create primary key on date_col and create nonclustered on idno.
...

1. It is a good practice to use composite primary key where it is appropriate

2. It is a bad practice to use composite primary key where it is not appropriate

3. If your date_col can only contain unique values it's fine to make it PK

4. Cou can create nonclustered index on any column including idno

5. You can make PK to be non-clustered and create clustered key on other column, but I can't see that your sample has a case for this.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I generally avoid composite primary keys, however, it depends..

I almost always build the primary clustered index on the single field that will always be unique. If my primary access to that table is through another field or fields, I may consider building the clustered index on that set of fields. but I don't take that path often.

Also, depending on the data and how its written it could be entirely likely that although you generally access it through the date that the idno is ok to be the primary clustered index and a non-clustered index on the date is ok.

In the end a non-clustered index references the entries in the clustered index.

CEWII

SriSun Say Hey Kid Points: 684 More actions · Answer 4

Hi All,

Thank you so much for your valueable information.

Sri.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Watched only 10 minutes of it but it goes in deep details about this issue... and it's from an MCM ;-).

http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx