• Adam Thurgar

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.


6 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t