Deleting Rows
What happens when rows are deleted from a table? How, and when, does SQL Server reclaim the space when data is removed from a table?
Deleting Rows from a Heap
In a heap table, SQL Server does not automatically compress the space on a page when a row is removed; that is, the rows are not all moved up to the beginning of the page to keep all free space at the end. To optimize performance, SQL Server holds off on compacting the rows until the page needs contiguous space for storing a new row.
Deleting Rows from an Index
Because the data pages of a clustered table are actually the leaf pages of the clustered index, the behavior of data row deletes on a clustered table is the same as row deletions from an index page.
When rows are ...
Get Microsoft SQL Server 2014 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
