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?
-
Add the DDL for the table. And page splits occur every time a page fills up, no matter how the table is constructed. These are so called "good" page splits.Hannah Vernon– Hannah Vernon ♦2017年01月30日 20:51:42 +00:00Commented Jan 30, 2017 at 20:51
-
Some points: first, a clustered index will include all columns at the leaf level, no matter the clustering key. Second, fill factor is only used when creating or rebuilding an index. Third, physical and logical order of rows on a page can differ. Perhaps after a little more research you can clarify your question?Forrest– Forrest2017年01月30日 20:52:54 +00:00Commented Jan 30, 2017 at 20:52
-
1The answer is (usually, if there are a lot of extra columns): PAGE SPLITS GALORE! The answer as to why can be found elsewhere, here is a particularly good article for how to design a good clustered index and why: simple-talk.com/sql/learn-sql-server/…blobbles– blobbles2017年01月30日 20:53:37 +00:00Commented Jan 30, 2017 at 20:53
1 Answer 1
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.