Page Splitting? How it works?

Question

1. Does page splitting affect the physical sort of data in an index or the table in general? EXAMPLE: If you had a indexed email column and page splitting was occurring a lot, would the email addressed become out of order on the list, and now longer be in order?

2. How does page splitting/fragmentation work with a primary key record being deleted? EXAMPLE: If you have the following table with the primary keys being the numbers.

   1, bob, chair
   2, joe, table
   3, brandon, lamp
   4, jared, tv
   

Lets say you delete record 3. Now the table reads 1, 2, 4 (out of order), and then record 3 is re-inserted at a later time? It will then read 1,2,4,3. Does record 4 takes record 3's space or is it reserved in case record 3 is re-inserted at a later time? Or do you have to perform an index rebuild to physically re-sort it?

Thank you

Answer 1

1.) The clustered index is a logical order not physical, avoid trying to think of it as a real physical order, there are too many levels of abstraction between the index and the storage to consider physical order. (Extent allocation, Filegroups, Fragmentation on a disk / Lun, Lun's themselves - all these things make the notion of a physical order incorrect.)

Page splitting is handled in a way in which the logical order is maintained, the forward / reverse double linked list linking pages for a table are updated to account for the new page inserting it in 'order' so to speak.

2.) Physical order vs logical - again, just stop thinking about physical ordering, even if the rows are on the same page, the slot array of the page is the one maintaining the actual order of the data on the page itself. So while on the page it might end up 1,2,4,3, the slot array will be 4,3,2,1 (Slot array starts at the end of the page and is read backwards) - whether the space is re-used or not doesn't really matter, since it will handle the offset positions on the page vs order to be maintained via that array.

Impaler mentions using an auto-increment value for the primary key - the primary key and the clustered key do not have to be the same thing, while I would agree that you often use an identity field for a clustered key - the primary key should be considered separately based on needs and the candidate keys available.

How SQL Server works at a storage level can get quite a complex topic, personally I would recommend Kalen Delaney's book, although others are available.