Default data page splitting

The table sales has a clustered index on store_id, customer_id. There are three stores (A, B, and C). Each store adds customer records in ascending numerical order. The table contains rows for the key values A,1; A,2; A,3; B,1; B,2; C,1; C,2; and C,3, and each page holds four rows, as shown in Figure 2-2.

Figure 2-2: Clustered table before inserts

Image of two pages: page 1007 and page 1009. Page 1007 contains four rows, A1, A2, A3, and B1. Page 1009 contains four rows, B2, C1, C2, and C3.

Using the normal page-splitting mechanism, inserting “A,4” results in allocating a new page and moving half of the rows to it, and inserting the new row in place, as shown in Figure 2-3.

Figure 2-3: Insert causes a page split

Image shows what happens after a page split. Page 1007 and 1009 now have page 1129 between them. Page 1129 contains three rows, A3, A4, and B1.

When “A,5” is inserted, no split is needed, but when “A,6” is inserted, another split takes place, as shown in Figure 2-4.

Figure 2-4: Another insert causes another page split

Another page split and another page between pages 1129 and page 1009. The new page, page 1134, contains rows A5, A6, and B1.

Adding “A,7” and “A,8” results in yet another page split, as shown in Figure 2-5.

Figure 2-5: Page splitting continues

Image representing more page splitting. Page 1137 is the result of pages 1134 and page 1009 splitting. Page 1137 contains rows A7, A8, and B1.

For more information about data page splits, see Chapter 12, “How Indexes Work,” in the Performance and Tuning: Basics