Changing Non-Clustered Index to Clustered

Hi, I want to change a non-clustered index to a clustered index (on the PK).
But when I try doing this through EM, I get "Cannot convert a clustered
index to a nonclustered index using the DROP_EXISTING option". When I
remove the DROP EXISTING option, I still get an error. I should mention
that there a lot of other non clustered indexes on this table.
How can I change this to use a clustered index?
Thanks.Remove the foreign keys that refers to this table, drop the primary key,
re-create it as a non-clustered index and add back the foreign key. Sorry,
but those are the steps... :-(
Also, make sure no user is in the db as you will be without FK's for a
while.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SQL" <nospam@.asdfadsf.com> wrote in message
news:e3TVU217DHA.1640@.TK2MSFTNGP11.phx.gbl...
> Hi, I want to change a non-clustered index to a clustered index (on the
PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.
>|||Probably the simplest way is to do this in Enterprise Manager. You can
make the change and save it, or make the change and view/save the script
it produces. You can then see all the steps that are required in your
particular case.
HTH,
Gert-Jan
SQL wrote:
> Hi, I want to change a non-clustered index to a clustered index (on the PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.