Sql-server – Clustered index data page behavior on inserts if all columns make up the index

sql server

What really happens at the data page level if you have, for example, a 5 column table with all 5 of those columns being part of the primary key and you cluster the PK so every column is part of the index?

You expect 1000’s of inserts on the table and you arbitrarily set fill factor to 80%. Since the pages have 20% free space for the inserts, what happens during the inserts to the records that already exist on the page and the new record would fits in between them? Does a page split occur or does the rest of the records move on the page or something else?

Best Answer

It's not clear to me how your first paragraph relates to the second, but I can answer the following bit:

Since the pages have 20% free space for the inserts, what happens during the inserts to the records that already exist on the page and the new record would fits in between them? Does a page split occur or does the rest of the records move on the page or something else?

When there is enough free space on the page for the record being inserted (whether in the middle or at the end), there will be no page split, as the page can simply be rewritten with the new data inserted.
If there is insufficient space, then there will be a page split, with approx half the data moved to a new page.
Note that if the insert is to the end of the very last page, and there's no space, then a new page will be created just for the new row and subsequent appends. That's what's known as a "good" page split, though it really isn't a split at all even though SQL server counts it as one.