Hugo Kornelis (12/26/2012)

---

Basics? That's not how I would describe it! :w00t:

Thanks for a good question, Sean. I think I once read or heard that after deleting all rows from a page, a cleanup process will eventually free up the page. But I am not sure; in fact, the more I think about it, the more I start to think I am confusing things.

Anyway, can you shed some more light on this?

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database. To delete rows in a heap and deallocate pages, use one of the following methods.

Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take a shared lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).

Use TRUNCATE TABLE if all rows are to be deleted from the table.

Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.