Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Primary Keys Expand / Collapse
Author
Message
Posted Wednesday, March 10, 2010 9:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #880342
Posted Wednesday, March 10, 2010 9:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 21,751, Visits: 15,449
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880344
Posted Wednesday, March 10, 2010 9:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 21,751, Visits: 15,449
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #880349
Posted Wednesday, March 10, 2010 9:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:51 PM
Points: 880, Visits: 607
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.'
Post #880351
Posted Wednesday, March 10, 2010 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
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.



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)
Post #880364
Posted Wednesday, March 10, 2010 10:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,580, Visits: 3,834
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".
Post #880416
Posted Wednesday, March 10, 2010 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #880432
Posted Wednesday, March 10, 2010 11:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:51 PM
Points: 880, Visits: 607
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.'
Post #880458
Posted Wednesday, March 10, 2010 1:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #880514
Posted Monday, March 15, 2010 12:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 8,832, Visits: 9,389
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
Post #883237
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse