October 4, 2005 at 3:57 pm
Hi,
I have two questions in sql server 2000 and I really appreciate if anybody help me.
1) How to drop a clustered index on primary key.
2) How to create a cluster index on some other column other than primary key and nonclustered index on primary key.
Thanks,
Bhushan Kalla
October 4, 2005 at 4:27 pm
Primary key is a constraint. So you have to drop the primary key constraint. then create a new one that is not clustered. then create the index you want.
use sp_help tablename
and
sp_helpIndex tablename
If you need help finding index/constraint names.
here's an example
create table mytable
(pk int identity,
col1 char(1)
constraint [clusteredindexkey] primary key clustered
(
[pk]
) on [primary]
)
exec sp_helpindex mytable
-- Results
clusteredindexkey clustered, unique, primary key located on PRIMARY pk
-- Drop primary key constraint
alter table mytable drop constraint clusteredindexkey
-- now table does not have primary key, or index
-- Create new primary key nonclustered
alter table mytable
add constraint NONclusteredindexkey
primary key
nonclustered
(pk) on [primary]
exec sp_helpindex mytable
-- Results
NONclusteredindexkey nonclustered, unique, primary key located on PRIMARY pk
-- Create the other clustered index
create clustered index clustedredothercol on mytable (col1)
exec sp_helpindex mytable
-- Results
clustedredothercol clustered located on PRIMARY col1
NONclusteredindexkey nonclustered, unique, primary key located on PRIMARY pk
drop table mytable
October 5, 2005 at 3:19 pm
Thank you very much Ray... Your example is very nice and easy to understand. It was really useful to me.
Once again thanks for your time and help.
Bhushan Kalla
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply