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 12»»

Primary key without cluster index Expand / Collapse
Author
Message
Posted Thursday, July 15, 2010 1:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, 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
Post #952895
Posted Thursday, July 15, 2010 1:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #952900
Posted Thursday, July 15, 2010 1:53 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:03 PM
Points: 687, Visits: 1,110
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?

Post #952904
Posted Thursday, July 15, 2010 2:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 42,458, Visits: 35,518
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 2008, MVP
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

Post #952911
Posted Thursday, July 15, 2010 5:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi,

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

Koteswarrao.y

Post #953006
Posted Thursday, July 15, 2010 5:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi buvensh,

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

Koteswar rao
Post #953015
Posted Thursday, July 15, 2010 9:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 1,194, Visits: 2,210
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
Post #953227
Posted Monday, July 19, 2010 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #954642
Posted Tuesday, July 20, 2010 6:10 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: 2 days ago @ 5:58 AM
Points: 905, Visits: 1,414
--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



Post #955472
Posted Tuesday, July 20, 2010 6:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 19, 2013 7:41 AM
Points: 1,626, Visits: 477
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).
Post #955476
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse