Creating Non Cluster Index on Primary Key

Question

Creating Non Cluster Index on Primary Key

Dharmendra Mudaliar

Right there with Babe

Points: 796
More actions
December 16, 2004 at 5:37 am

#102313

Dear All
Can i create a Non Cluster Index on a Primary Key. I am a little bit confused for it. Pls help me out.
Any help would be greatly appreciated.
Regds
Dharmendra
Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com

Viewing 7 posts - 1 through 6 (of 6 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

The fact that this is default behaviour of SQL Server, does not mean that it cannot be changed. Just explicitely created the PK as nonclustered, like so:

create table #t

(

c1 int primary key nonclustered

...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dharmendra Mudaliar Right there with Babe Points: 796 More actions · Answer 2

Dear Frank

Thanx for ur reply. But my Question is I am already having a Primary Key on a particular column. And now i wanted to create Non Cluster Index on the table. Is is possible to do that if yes then how reply.

Regds

Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

OKay, in that scenario where a clustered PK is already present, you might want to have a look at CREATE INDEX in BOL. SQL Server allows to have more than 200 nonclustered indexes for each table. More than enough. Hope I understood you now correct.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dharmendra Mudaliar Right there with Babe Points: 796 More actions · Answer 4

Dear Frank

Thanx for ur quick and immediate reply. But can u tell me what is BOL. where i can check my the Solution

Regds

Dharmendra

Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

BOL is SQL Server's very good online help. You should find it in your Microsoft SQL Server program group. Otherwise you can download it for free from the MS website. Unlike other MS products, this online help is really good and usually the first place to look when you have question. You type in there "CREATE INDEX" and you'll find a whole bunch of explanations and examples.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

PBirch SSCrazy Eights Points: 8493 More actions · Answer 6

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

This should help.

Quand on parle du loup, on en voit la queue