Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John