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

Question

If I have a clustered index on a table is it safe to delete, and if I do, does it leave the table ordered the same way it was while indexed?

Answer 1

If is safe to delete it (as long as data integrity is concerned and the index is not UNIQUE).

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

Note that the table is not "ordered" initially. When you issue this query:

SELECT  *
FROM    mytable

, the rows are not guaranteed to come in the index order unless you use ORDER BY clause.

Answer 2

It's always safe to delete technically, but whether it makes sense in a design/architecture/performance prespective we can't say.

The data will remain in the order on disk until update/inserts happen, but please don't rely on that at all, ever. Output is only guaranteed whan you use an ORDER BY in the outermost SELECT