Database Reference
In-Depth Information
Page Split by an INSERT Statement
To understand how a page split can be caused by an
INSERT
statement, create the same test table as you did
previously, with the eight initial rows and the clustered index. Since the single index leaf page is completely filled, any
attempt to add an intermediate row as follows should cause a page split in the leaf page.
INSERT INTO Test1
VALUES (110, 'C2', '');
You can verify this by examining the output of
sys.dm_db_index_physical_stats
(Figure
13-9
).
Figure 13-9.
Pages after insert
As explained previously, half the rows from the original leaf page are moved to the new page. Once space is
cleared in the original leaf page, the new row is added in the appropriate order to the original leaf page. Be aware that
a row is associated with only one page; it cannot span multiple pages. Figure
13-10
shows the resultant distribution of
rows in the two pages.
Figure 13-10.
Page split caused by an INSERT statement
