Nonclustered indexes and insert operations

When you insert rows into a heap that has a nonclustered index and no clustered index, the rows are inserted in the last page of the table.

If the heap is partitioned, the insert goes to the last page on one of the partitions. Then, the nonclustered index is updated to include the new row.

If the table has a clustered index, the clustered index is used to find the location for the row. The clustered index is updated, if necessary, and each nonclustered index is updated to include the new row.

Figure 5-9 shows an insert into a heap table with a nonclustered index. The row is placed at the end of the table. A row containing the new key value and the row ID is also inserted into the leaf level of the nonclustered index.

Figure 5-9: An insert into a heap table with a nonclustered index

Image shows a series of pages starting at the root page and ending at the data pages on a heap table. When a row is inserted into the data pages, another row is inserted  into the leaf-level index.