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


Primary key without cluster index


Primary key without cluster index

Author
Message
y.koteswarrao-652921
y.koteswarrao-652921
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: 199
Hi,

This is an interview question can we create a primary key on table without cluster index?

I think it not possible.............Can we forceble ignore the cluster index while creating the primary key...

Koteswarrao
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
Yes you can create

ALTER TABLE dbo.RFP_NSO_SEND_QUEUE ADD CONSTRAINT
PK_RFP_NSO_SEND_QUEUE_rfp_nso_send_queue_stub PRIMARY KEY NONCLUSTERED
(
rfp_nso_send_queue_stub
) WITH( FILLFACTOR = 75, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARYDATA]
GO



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Jim McLeod
Jim McLeod
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1121
The best way of learning interview questions is to give it a shot.

Here's the relevant syntax from Books Online (from the article on CREATE TABLE):
[ CONSTRAINT constraint_name ] 
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}



Here's another fun question - what's the difference between a primary key and any other unique key, regardless of whether it's clustered or not?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47241 Visits: 44377
y.koteswarrao-652921 (7/15/2010)
This is an interview question can we create a primary key on table without cluster index?


Yes.

I think it not possible.............Can we forceble ignore the cluster index while creating the primary key...



Primary key and clustered index are different concepts. Primary key is the row's identifier. Clustered index is an index with the data pages at the leaf level.

Only relation is that, by default, the primary key is enforced by a unique clustered index. This is only by default, not by requirement.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


y.koteswarrao-652921
y.koteswarrao-652921
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: 199
Hi,

thanks a lot..........for your explanation...........iam clear about it.

Koteswarrao.y
y.koteswarrao-652921
y.koteswarrao-652921
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: 199
Hi buvensh,

Thank for your reply............iam clear about with your reply....

Koteswar rao
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
PK by default creates a unique clustered index if one doesn't already exists.

If a clusterd index already exists, you can specify NON CLUSTERED clause to create a noon clustered index.

Thank You,

Best Regards.
SQLBuddy
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
sqlbuddy123 (7/15/2010)
PK by default creates a unique clustered index if one doesn't already exists.

If a clusterd index already exists, you can specify NON CLUSTERED clause to create a noon clustered index.

Thank You,

Best Regards.
SQLBuddy
Well said , i missed this point

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Steve-3_5_7_9
Steve-3_5_7_9
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1583
--soap box
I think the principle issue with misconceptions is that people are using the Table Designer in Management Studio. They "click" they "key" icon which will create the PK as a clustered index. By knowing this and knowing the difference between PK and CL as mentioned above you can still use the "designer" to create/modify your tables, just use"Manage Index and Keys" instead of the "PK" icon.

Personally I think people should be taught scripting objects first and the fundamental concepts of db design then when using the "designers" and "wizards" they will have a better grasp and will know exactly what they want when designing their objects.

Somehow this is being lost in the curriculum of where ever people are learning about database concepts.

--End of soap box
Steve



Source-NH
Source-NH
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: 1631 Visits: 479
Jim McLeod (7/15/2010)

Here's another fun question - what's the difference between a primary key and any other unique key, regardless of whether it's clustered or not?



PK allows NO nulls in key columns, where a Unique constraint will allow records that contain a null in the key columns (still must be unique, however).
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