SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Primary Keys


Primary Keys

Author
Message
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3663 Visits: 1717
Comments posted to this topic are about the item Primary Keys

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Ric Sierra
Ric Sierra
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 Visits: 342
Tricky question!
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32503 Visits: 18556
Nice question. Keep 'em coming.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 1803
A thought provoking question, but I'm not too keen on the answer. Unless Paul would like to pay for my airfares???

S.
sameerchachad 69959
sameerchachad 69959
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 41
A Primary Key is always enforced by a clustered index at creation?
Correct answer (SQL Server Central) : False

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.

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.

Kindly reply if there is something else to add or I haev misquoted anywhere.

Warm regards,
Sameer Chachad.
Anil KK
Anil KK
Say Hey Kid
Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)Say Hey Kid (699 reputation)

Group: General Forum Members
Points: 699 Visits: 265
hi sameer,

nice question.... you r question it self has the answwer..

The actual question asked is

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

which is not always done, it is enforced by a clustered index only when there is no clustered index existing on that table. If there is an clustered index existing in that table, the primary key u r going to create will be enforced by a non clustered index.

please notice the word "always" in the question...

:-)

Anil Kubireddi
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1996 Visits: 3069
A Primary Key is created by defaut as a Clustered Index, unless a Clustered Index already exists on the table, which is really to say that the Clustered Index does not have to necessarily be the Primary Key. It is the semantics of how you are phrasing the question that is a little tricky and confusing for some. However, as you state Paul in your explanation, the more important piece to emphasize here is the reasoning of where you decide to put your Clustered Index on your table. Application functionality can drive this at times and a PK is not always the first choice . That said, primary keys do tend to make make good candiates for the CI simply because they and their auto-incrementing data (singleton PK columns are many times IDENTITY columns as well, although not required to be) automatically satisfy two main requirements of a Primary Key. NO NULLS and Unique data, where Unique Keys can allow one NULL and are created Non-Clustered by default and can have more than one UK on a table I believe. Both PK's and UK's can be made up of more than one column as well. . Smile

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 11996
sameerchachad 69959 (3/9/2010)
A Primary Key is always enforced by a clustered index at creation?
Correct answer (SQL Server Central) : False

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.

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.

Kindly reply if there is something else to add or I haev misquoted anywhere.

Warm regards,
Sameer Chachad.


The answer given by Paul is correct but incomplete. The explanation correctly describes what happens if you do not specify a clustering option for the PRIMARY KEY constraint, but it doesn't include the extra possibilities that arise from adding the keyword CLUSTERED or NONCLUSTERED to the constraint.

These are the scenarios that are possible when a PRIMARY KEY constraint is created:

1) No clustered index exists for the table, no clustering option specified for the constraint --> Clustered index is created. (This is probably the most common scenario)
2) No clustered index exists for the table, CLUSTERED option specified for the constraint --> Clustered index is created.
3) No clustered index exists for the table, NONCLUSTERED option specified for the constraint --> Non-clustered index is created.
4) A clustered index already exists for the table, no clustering option specified for the constraint --> Non-clustered index is created.
5) A clustered index already exists for the table, CLUSTERED option specified for the constraint --> No index created; error 1902 raised; constraint not created.
6) A clustered index exists for the table, NONCLUSTERED option specified for the constraint --> Non-clustered index is created.


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.

(edit: small but important typo in option 6 - thanks, Shaiju C.K., for alerting me to it)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
jshailendra
jshailendra
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 455
trickey question...already existing Clustered Index just went off from my mind and I answered TRUE...I was wondering why Paul would have asked such a simple question and w/o giving it a 2nd thought I happened to answered it wrong...:-D
nargade 3404
nargade 3404
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 58
Paul Randal,

When you ask\ write any question mention your assumption.
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