What are the benefits of not having a clustered index on a table in SQL Server?
SELECT * INTO TABLE_A FROM TABLE_B
Be faster if
TABLE_A is a heap?
Which operation(s) will benefit if the table is a heap?
I am quite sure
DELETEs will benefit from a clustered index. What about
INSERTs? My understanding is that
INSERT "might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage...).
What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with
DELETE? What cost goes up when table is a heap and we
DELETE from it?