Primary Keys

Question

Primary Keys

Viewing 15 posts - 16 through 30 (of 39 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 1

FYI, all of these came with Paul's name on them, so he gets to be the target 🙂

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Steve Jones - Editor (3/10/2010)
sameerchachad 69959 (3/9/2010)
I would just like to add a small point, whenever you create a Primary Key, by default its ALWAYS going to be Clustered. Unless you create a Non Clustered Index Primary Key.
I think you're emphasizing this a little wrong. The default setting is clustered, but you change that at creation time. So it's not that it always creates the index clustered, but that if you don't change the setting, that is what is done.

Correction - the default is not "clustered", but "clustered if no clustered index exists yet, otherwise nonclustered".

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

sistemas 95572 SSC Enthusiast Points: 135 More actions · Answer 3

Bottom line: always specify whether you want to create a clustered or nonclustered primary key on the [font="Courier New"]add constraint PK_..[/font] statement.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Still, a great question. I really hope that Paul and Kimberly continue to submit questions, even if they don't have a seminar to promote.

Heck either way. I've had the good fortune to listen to Kimberly and Paul for an about an hour and a half at a local user group meeting, and was more than willing to sit for a longer time, but alas, they had already spent a full day giving their seminar and were entitled to some rest / relaxation.

The amount of information these 2 individuals can impart, getting you to think ... is amazing. So let them promote their seminars, and if you have the opportunity to attend do so

Paul, Kimberly - thanks for a thought provoking question.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

I liked the question! I've found that in our environment the primary key is very often a bad choice for the clustered index, so I have become more aware of this "default" of late and been teaching those around me how to evalute good CI candidates and not just leave it at the default.

Thanks Kimberly!

Chad

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

Lynn Pettis (3/10/2010)
No assumptions needed. If you look at the syntax for creating a PRIMARY KEY, the default may be to create a clustered index, but that is not always how it is created. You can create it as a nonclustered index as well.

I totally agree. If the question is posted by Paul or Kimberly then it is usually a good, interesting question. This does not mean that the question is going to be difficult to answer, but it does not have to be. If one were to change one word in this QoD so it would read:

"A Primary Key is always enforced by a unique index at creation?"

then this would be a very lame, no-brainer question because it is true that the PK is ALWAYS enforced by a unique index provided that PK is created as a result of a DDL statement. As written, today's question is interesting, makes one think, and generates discussion.

Oleg

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

sameerchachad 69959 (3/9/2010)
A
So, when you are saying that IF a Clustered Index already exists, then your what you are mentioning is valid. Else the Primary key, which is usually created at when you are creating the table, would be Clustered index unless specified.

The default is if you use the GUI to create the PK. If you script it out, then you can also specify that it is enforced by an NCI. Also note, a PK is not automatically created when creating a table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Kimberly L. Tripp (3/10/2010)
Hey there everyone - I know people are feeling like was trying to trick them in the question but I guess the "always" part is what I thought would have been the dead give away. It's really rather simple actually - a Primary Key is NOT always enforced by a clustered index. ...
Cheers,
Kimberly

nargade 3404 (3/10/2010)
Paul Randal,
When you ask\ write any question mention your assumption.

Nargade,

Kimberly beat me to the punch with this answer of hers. The key word "always" really is a a giveaway to the answer. No assumptions needed. Then look at the syntax for creating a PK, and that explains it as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

weitzera Hall of Fame Points: 3442 More actions · Answer 9

A little bit of a different viewpoint: Most people have focused on the clustered vs nonclustered aspect of the question, but when I first read it, I thought the point was the enforcement. Of course a pk must be enforced, so I almost answered incorrectly. Thankfully I've learned that most of these questions are tricks, so I reread it...

Edit: I guess Oleg beet me to the punch on that one. Cheers!

As to the second question, Of course having the option for a nonclustered PK is a good thing!

So many people use a surrogate key (identity column) as their primary key, even when it doesn't make sense.

Clustering your table based on _accesses_ instead of arbitrary design decisions can be a good way to get some extra performance.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Personally, I think every table should have a surrogate key as either a Primary Key (PK) or Alternate Key (AK). Especially when using a natural key for the PK. You never know when a requirement change could result in a natural key no longer being a valid PK. By having a surrogate key as an AK, you still have a way to uniquely identify each and every record in the table.

OCTom SSChampion Points: 11785 More actions · Answer 11

I was told once by a wise man (my dad) that mutiple choice answers with "always" and "never" are usually incorrect and to not choose them. But, that doesn't "always" work; well. Hardly "never". 😛

Tom Garth SSCertifiable Points: 6193 More actions · Answer 12

There is nothing tricky about the question. ALWAYS enforced isn't the same as USUALLY enforced.

I was hoping for some discussion about the REAL question which was posed in the answer. "Is that a good thing?"

I can't think of the circumstance where it would be a good thing. I would like to hear about when it would be.

I wish I could make it to Boston, but that's not going to happen.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

weitzera Hall of Fame Points: 3442 More actions · Answer 13

regarding when the non-clustered pk might be good, the oversimplification is that if you do a lot of updates, you probably want the table clustered on the PK, because you probably select the records based on the pk. If you do a lot of selecting on ranges of values, e.g. reporting, you _might_ want to have a pk clustered to assist with that. To be clear, I _rarely_ override the clustering of a table, and it takes a lot of analysis to make sure you're doing it right, (and it's not hard to do it wrong) but it's not a tool I'd choose to give up.

As for surrogate keys, I was specifically thinking of a link table representing a many-many relationship.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

sjimmo SSChampion Points: 11139 More actions · Answer 14

Paul,

Very good question, but (yes there has to be right?)

When you use the term "default". By "default" SQL Server tries to create a PK as a clustered index. There already being an index on the table which is clustered is not necessarily a default action.

Otherwise, I enjoy your questions. They are thought provoking and good teaching points.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

TomThomson SSC Guru Points: 104773 More actions · Answer 15

Lynn Pettis (3/10/2010)
Personally, I think every table should have a surrogate key as either a Primary Key (PK) or Alternate Key (AK). Especially when using a natural key for the PK. You never know when a requirement change could result in a natural key no longer being a valid PK. By having a surrogate key as an AK, you still have a way to uniquely identify each and every record in the table.

And sometimes, when space occupied by pointers into the table is less important than other considerations (such as space occupied by this table, perhaps), it's good to tack the surrogate key column onto the end of the compound primary key so that it will always remain unique. Not something that makes sense often, but in the rare cases when it does make sense it can give quite a boost.

Tom