|
|
|
SSC 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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
|
|
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---------- While 1 = 1 (Learning SQL....) Click to get fast response of your post
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:57 PM
Points: 687,
Visits: 1,075
|
|
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?
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741,
Visits: 30,020
|
|
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
|
|
|
|
|
SSC 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
|
|
|
|
|
SSC 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
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:17 PM
Points: 715,
Visits: 1,524
|
|
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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
|
|
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---------- While 1 = 1 (Learning SQL....) Click to get fast response of your post
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 810,
Visits: 1,195
|
|
--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
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:07 AM
Points: 1,619,
Visits: 473
|
|
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).
|
|
|
|