Heap tables (tables without a clustered index) are generally not part of a good database design.
Unless you never actually query your table, you should always put a clustered index on it. Heap table are generally slower on selects, updates and deletes. They are also generally slower on inserts if you decide to use a nonclustered index instead of a clustered index (which you shouldn’t do).
By default, Management Studio creates a clustered primary key so heap are sometimes created because no primary key has been defined on a table. A primary key is NOT the same thing as a clustered index. A primary key cannot have duplicate or null values but a clustered index certainly can.
Moreover, a heap table is way bigger and has generally high fragmentation. Fragmentation occurs on non heap tables but you can at least rebuild your index to get rid of it.
If you have a log table that almost never gets queried and you don’t care about disk space then you could use a heap table. If you need a table to insert large sets of data before creating a clustered index you should create a temporary heap table for performance on inserts. Otherwise, always define a clustered index on all your tables.
Nice article on performance of heap+index versus clustered index: http://msdn.microsoft.com/en-us/library/cc917672.
RSS