Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you talking about SQL Server? –  a_horse_with_no_name Nov 8 '12 at 12:57
    
@a_horse_with_no_name yes, i forgot to mention that sry –  marc.d Nov 8 '12 at 16:10
    
See also stackoverflow.com/questions/1341393/…. –  Jon of All Trades Sep 6 '13 at 15:25
add comment

2 Answers

up vote 10 down vote accepted

The only valid use is for staging tables used in import/export/ETL processes.

These table 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:

Also see

share|improve this answer
2  
It typically use heaps for two separate things. ETL staging and work tables that I use to temporarily store data when the set is to large for a temp table to work effectively. All of which are truncated at next load. –  Zane Nov 9 '12 at 17:12
    
Good question by the way. –  Zane Nov 9 '12 at 17:12
add comment

Heap tables are good for tables with millions of rows that are hit heavily by users. The downside is that they can take up a lot of space because the data is physically stored unsorted. Also, you rely on your indexes to be tuned to your queries. I have worked in places that did not use clustered indexes at all because of performance problems. Probably due to poor clustered index choices but if you just use heap tables you don't have to worry about it.

A better solution would be to use the enterprise edition of sql server and horizontally partition the large table. But if you don't have the enterprise edition the heap table is the way to go for large tables.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.