We help IT Professionals succeed at work.

remove cluster index.

I want to remove cluster index from a table. However, it seem like I have to remove all the data only then I can remove the cluster index from that table. is there any better way. thanks.
Comment
Watch Question

Commented:
Do you get a error message when  you try to drop it?  If so the only thing I can think of right now is for you to make a copy of the table.  When you make a copy of the table, no indexes are created.
Nothing to stop you dropping a clustered index.
It will rebuild all the other indexes as they refer to the clustered index - maybe you are getting a problem with that - could drop all the other indexes first then add them.

If the clustered index is a primary key and referred to by foreign keys then that would stop you.

try

drop index tblname.indexname

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
It really does sound like a Foreign Key issue.  Issue:
EXEC sp_fkeys 'table_name'

to check for foreign key(s) on the main table and any tables that may be related.  If you find a relationship based on the clustered key columns you are trying to remove, you can temporarily disable those constraints via:
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name

Then, after putting the column back, you can reverse it bys issuing:
ALTER ... CHECK ...
provided you used the same column name.  Otherwise, you'll have to DROP and (re)CREATE the constraint.
waho:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.