PK

Question

Post reply

PK

ramana3327

SSCoach

Points: 19700
More actions
April 22, 2014 at 11:16 am

#288155

Hi All,
In what situations we can create primary key on a table? I mean what is the minimum no of rows we can prefer to create PK.
Thanks,
Ramana

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The minmum number to prefer is Zero, meaning the PK should be part of the initial table definition.

What makes you think there's a relation to the number of rows in a table?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

Perhaps the question is more, "What is minimum number of rows threshold where a PK starts to pay off?"

Seems to me, if a table only ever has 1 row, a PK is a waste. If there are a million rows, a PK is probably a good idea. Somewhere between 1 and a million there ought to be a break-even point where the benefit of a PK outweighs the cost of maintaining it.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

gbritton1 (4/22/2014)
Perhaps the question is more, "What is minimum number of rows threshold where a PK starts to pay off?"
Seems to me, if a table only ever has 1 row, a PK is a waste. If there are a million rows, a PK is probably a good idea. Somewhere between 1 and a million there ought to be a break-even point where the benefit of a PK outweighs the cost of maintaining it.

A waste of what? The only time it would come into play is during the 1 and only 1 insert. Not exactly a resource hog. While it is true that a PK on a 1 row table is overkill even putting the notion out there that less than a million rows is ridiculous.

Unless you plan on putting a clustered index on the table that is not the primary key the real answer of course is "it depends". You could probably get away using a unique constraint on a table that is maybe 10 rows but the performance benefit between the constraint and a primary key is so minor you can't even really measure the difference. Any table with even a few rows will benefit from a primary key.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

LutzM SSC Guru Points: 107049 More actions · Answer 4

Any table with even a few rows will benefit from a primary key.

Especially if the value is used in other tables so we'd have a PK/FK scenario.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

"A waste of what?"

...a waste of time thinking about it.

bartedgerton SSCrazy Points: 2062 More actions · Answer 6

Why have a table with 1 row? Are the developers to lazy to make a config file?

ALL tables should have a primary key, period. Without it, you've just got a closet full of scattered shoes, and all your user's standing in line trying to find theirs. Even your single row table will benefit from a primary (unique) key, even if it's a surrogate key that is never referenced by a single query, it will improve performance and improve data consistency.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

gbritton1 (4/22/2014)
Perhaps the question is more, "What is minimum number of rows threshold where a PK starts to pay off?"

0

Primary key is part of your table design. It's the unique column or set of columns which identifies the row. It's not something to optionally create later, it should be defined at the time the table is designed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19700 More actions · Answer 8

Thanks for all replies.

In case with the table structure they didn't define the PK, then surely we can give suggestion to modify that table by creating the PK. We don't need to worry about the no of columns.