Clustered indexes and delete operations

When you delete a row from an allpages-locked table that has a clustered index, other rows on the page move up to fill the empty space so that the data remains contiguous on the page.

Figure 5-5 shows a page that has four rows before a delete operation removes the second row on the page. The two rows that follow the deleted row are moved up.

Figure 5-5: Deleting a row from a table with a clustered index

Series of data pages on a table with a clustered index. After deleting a row, the pages below the deletion move up in page number, removing the numbering gap.