Primary key without cluster index

Question

Post reply

Primary key without cluster index

y.koteswarrao-652921

SSCrazy

Points: 2010
More actions
July 15, 2010 at 1:33 am

#145869

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

Viewing 15 posts - 1 through 15 (of 19 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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 SSCarpal Tunnel Points: 4057 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSCrazy Points: 2010 More actions · Answer 4

Hi,

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

Koteswarrao.y

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 5

Hi buvensh,

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

Koteswar rao

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

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 SSC Guru Points: 59351 More actions · Answer 7

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 SSChampion Points: 10832 More actions · Answer 8

--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 SSCarpal Tunnel Points: 4165 More actions · Answer 9

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).

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

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?

A primary key cannot contain NULLs (as already mentioned).

You can only have one primary key per table.

A primary key is clustered by default.

Unique indexes can be filtered (in SQL Server 2008).

A unique index can be altered.

A unique index can be created on a view.

There's probably lots of other differences too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 11

Sorry to be the kiss @$$ looking for an "A" 😀

but only non-clustered indexes can be filtered

Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Bradley B (7/20/2010)
Sorry to be the kiss @$$ looking for an "A" 😀
but only non-clustered indexes can be filtered

A unique index can be filtered, providing it's a nonclustered index. Neither a unique constraint nor a primary key can be, regardless of what kind of index enforces them.

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

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 13

Yeah okay back down to a B, but heck you only need a C average to be president

I was waiting for it after I re-read and saw it was unique and not clustered. Agreed though you can have a unique non-clustered filtered index, but you could not have a filtered clustered index (primary key or no primary key)

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

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

Bradley B (7/20/2010)
I was waiting for it after I re-read and saw it was unique and not clustered.

I knew what I meant 😛 but yes, I should have said "non-clustered" to avoid ambiguity.

Agreed though you can have a unique non-clustered filtered index, but you could not have a filtered clustered index (primary key or no primary key)

Seeing as I am now in picky mode (you started it) I'm going to say that a filtered clustered index is possible (in a sense) just create it on a view that contains the filter condition 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi