Inserting Data

When you add a data row to a heap table, SQL Server adds the row to the heap wherever space is available. SQL Server uses the IAM and PFS pages to identify whether any pages with free space are available in the extents already allocated to the table. If no free pages are found, SQL Server uses the information from the GAM and SGAM pages to locate a free extent and allocate it to the table.

For clustered tables, the new data row is inserted to the appropriate location on the appropriate data page relative to the clustered index key order. If no more room is available on the destination page, SQL Server needs to link a new page in the page chain to make room available and add the row. This is called a page split.

In addition to ...

Get Microsoft® SQL Server 2008 R2 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.