Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Primary Keys


Primary Keys

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

weitzera
weitzera
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 629
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
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". :-P
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
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

"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
weitzera
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 629
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
sjimmo
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3352 Visits: 2846
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
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10714 Visits: 12017
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search