In a previous post I mentioned heap tables and since then I decided that they deserve their own post.
Heaps tables can be:
- tables without any indexes
- tables with non clustered index/es but no clustered index.
It may be okay for small tables to be a heap.
My rule of thumb is, for tables using up to one extent (64kb).
Or you could say tables with less than 100 rows.
- Tables with an existing index/es, but no clustered index should be changed to have a clustered index.
- Tables with no indexes at all, should have a clustered index added.
In this case you really need to understand the difference between a clustered index and non clustered indexes and the effect these have on the optimizer with index seek, index scans and key lookup operations.