What are valid usage scenarios for HEAP tables?

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and now I am curious in what usage scenarios a HEAP table should be preferred over a clustered table?

As far as I understood a HEAP table would only be useful for audit tables and/or where inserts happen far more often than selects. It would save disk space and disk I/O since there is no clustered index to maintain and the additional fragmentation wouldn’t be a problem because of the very rare reads.

The only valid uses are for

• staging tables used in import/export/ETL processes.

• ad-hoc, temporary and short term backup of tables using SELECT * INTO..

Staging tables are typically quite flat and truncated before/after use.

Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure.

Heap tables also have problems. At least these:

• can not be defragmented to reduce space on disk. This matters because used data pages will be scattered throughout the MDF for example, because data has no "order" from the clustered index

• non-clustered index now point to the row, not the clustered index entry. This affects performance: Need for reaching data through clustered index with a non-clustered index

Also see

• http://www.sqlbadpractices.com/heap-tables/ (Bad practice)

• http://msdn.microsoft.com/en-us/library/hh213609.aspx (MS's recommendations)

• http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx (Paul Randal's DBA myths)

StackExchange Database Administrators Q#28370, answer score: 22