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


Primary Keys


Primary Keys

Author
Message
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7828 Visits: 1719
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1583 Visits: 342
Tricky question!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63925 Visits: 18570
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
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1809
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 (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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 (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 270
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3252 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18283 Visits: 12426
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
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 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
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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