Primary Keys

Question

Post reply

Primary Keys

Viewing 10 posts - 31 through 39 (of 39 total)

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

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 1

When are Kim and Paul coming to Atlanta... this sounds like a great lecture.

Jamie

Paul White SSC Guru Points: 150467 More actions · Answer 2

Good question, but the wording could have been clearer. Nice easy points though - thanks.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 3

Hi Hugo,

The 3rd and 6th options seems similar.

Shaiju C.K.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

C.K.Shaiju (4/1/2010)
Hi Hugo,
The 3rd and 6th options seems similar.
Shaiju C.K.

You are right! Thanks for pointing it out. The sixth option should have read "A clustered index exists ..." instead of "No clustered index exists ..."

I have edited my post to prevent future confusion.

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/

parthenitin Ten Centuries Points: 1190 More actions · Answer 5

When we creates the primary key it alwas enforce to create as Clustured.

how it will enforce to create as NonClustered.

Please Can u tell me.

Paul White SSC Guru Points: 150467 More actions · Answer 6

parthenitin (4/3/2010)
When we creates the primary key it alwas enforce to create as Clustured.
how it will enforce to create as NonClustered.
Please Can u tell me.

Use the NONCLUSTERED keyword.

See Creating and Modifying PRIMARY KEY Constraints

Example:

DECLARE @Table

TABLE (

column1 INTEGER NOT NULL

PRIMARY KEY NONCLUSTERED

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 7

parthenitin (4/3/2010)
When we creates the primary key it alwas enforce to create as Clustured.
how it will enforce to create as NonClustered.
Please Can u tell me.

In addition to the reply by Paul -- if there is already a clutered index on the table, then the index for the primary key will also default to 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/

Kimberly L. Tripp SSC Journeyman Points: 89 More actions · Answer 8

Thanks Hugo. That was the main one (and point) I was making.

Often people use scripts to create objects but there are strange cases where multiple developers working on the same project creates... er... "issues" and the indexes aren't quite as consistent as expected. Constraints are often created via scripts but often as standalone scripts (not always as part of the table's create) and sometimes problems can occur when there are too many cooks in the kitchen. 😉

Cheers!

Kimberly L. Tripp

SQL Server MVP, Microsoft Regional Director

blog: http://www.SQLskills.com/blogs/Kimberly

twitter: http://www.twitter.com/KimberlyLTripp

khullargirish02 SSChasing Mays Points: 651 More actions · Answer 9

False, if a clustered index already exists, DB will enforce the Primary Key constraint with a nonclustered index

khullargirish02 SSChasing Mays Points: 651 More actions · Answer 10

if a clustered index already exists, SQL Server will enforce the Primary Key constraint with a nonclustered index