Heaps

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.

Otherwise

- 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.