Geek Answers Handbook

Is it safe to delete a clustered index on a SQL server?

If I have a clustered index in a table, is it safe to delete, and if so, will it leave the table ordered in the same way as when indexing?

+4
source share
2 answers

It is always safe to remove technically, but does it make sense in a design / architecture / performance that we cannot talk about.

The data will remain in order on the disk until an update / insert occurs, but please do not rely on it at all. The output is guaranteed only by using ORDER BY in the most remote SELECT

+2
source

If it is safe to remove it (if the data integrity and index are not UNIQUE ).

When you delete the CLUSTERED index, the table becomes an organized heap (i.e. table rows are no longer part of the B-Tree ), and all other indexes are rebuilt to refer to the RID instead of index value + uniquifier .

Note that the table is not "ordered" at first. When you issue this request:

 SELECT * FROM mytable

rows are not guaranteed in index order unless you use the ORDER BY .

+3
source

More articles:


All Articles