The Curious Case of: the empty heap table

Question: We’re using heaps for some of our tables in a a new database for a variety of reasons and we’ve found a strange phenomenon where if we delete lots of data from the tables, the amount of space used by the tables does not go down. What’s going on?

Answer: The behavior you’re seeing is how SQL Server works, but it’s non-intuitive.

Whenever a row is deleted in a heap, if the page that the row is stored on becomes empty, the page cannot be deallocated from the table unless an exclusive table lock is held. This is usually not the case unless lock escalation occurs because you’re deleting enough rows to trigger escalation, or if you specifically use the TABLOCK hint on the delete statement.

There is a Knowledge Base article that describes this phenomenon – KB 913399. However the KB article only references up to and including SQL Server 2005. This behavior exists in more recent releases too and is very easy to reproduce. The script below will generate the scenario (I was running on SQL Server 2008 SP2):

PaulsDB; GOGOCREATE TABLE t1 (c1 CHAR (8000) DEFAULT 'a');GOINSERT INTO t1 DEFAULT VALUES;GO 100 DELETE FROM t1;GOGO

The DBCC IND output gives one row per page allocated to the table and the script above returns 101 rows (100 data pages and 1 allocation bitmap page). If you use DBCC PAGE to examine any of the data pages you’ll see that they’re all empty.

If that space isn’t going to be reused, I would consider avoiding this phenomenon through one of the workarounds (use TABLOCK, for instance) or simply use a clustered index instead.