Clustered indexes and insert operations

When you insert a row into an allpages-locked table with a clustered index, the data row must be placed in physical order according to the key value on the table.

Other rows on the data page move down on the page, as needed, to make room for the new value. As long as there is room for the new row on the page, the insertion does not affect any other pages in the database.

The clustered index is used to find the location for the new row.

Figure 5-2 shows a simple case where there is room on an existing data page for the new row. In this case, the key values in the index do not need to change.

Figure 5-2: Inserting a row into an allpages-locked table with a clustered index

Images of a series of pages with rows being inserted. The key values remain the same during the inserts.