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

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clustered index now, I would like to change the create_dt as clustered index. When I do reindex, will it block the table? How long will it take? Can I do it in EM from the Design able? ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clust
ered index now, I would like to change the create_dt as clustered index. Whe
n I do reindex, will it block the table? How long will it take? Can I do it
in EM from the Design able?
ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks