Page split where existing rows are moved to different page occurs only for updates and non-sequential inserts?

[jules]

Suppose page is full, and an item is inserted in order, then that item will get added to the next page. There will never be page split such that some rows are placed on a page and some rows move to another page.

Where as if an update is performed such that an item needs to be placed somewhere in an already full page, then a page split happens where some rows are kept of this page and other rows are moved to a new page.

So a page split where existing rows are moved to different page occurs only for updates and non-sequential inserts?

[Raziyah00]

For a SQL Server rowstore index, there are two kinds of page splits. Others have mentioned, as has the poster of the question, that updates or inserts in the interior of the logical order of the index can lead to an interior page split due to exceeding the limit of the 8k page. That's one type of page split.

The second type of page split for a rowstore index occurs at the logical end of the index. As the last 8k page in index logical order reaches it's fullest state and the index spills into the next page - that is a page split. It is not as intensive of an operation as the split of a page from the interior of the index. And while an interior page split will usually leave the rowstore index with less logical contiguity and higher reported index fragmentation (as reported by sys.dm_db_index_physical_stats), a page split at the end of the index logical order will not do so(usually although some conditions of limited unallocated extents in the database file(s) may occasionally lead to this).

Having established that both INSERT and UPDATE can lead to page splits in rowstore indexes, what about DELETE?

Yes, even DELETE can lead to rowstore index page splits. Interior page splits (the more intensive, less desirable kind) or a page split at the logical end of the index.

For an outstanding explanation of how row versioning + DELETE can result in page splits, I recommend the blog post referenced below from Paul White. Basically, when row versioning is introduced to a database page that previously did not have to accomodate it, the 14 byte versioning info per row can result in a page split.

Deletes that Split Pages and Forwarded Ghosts 2012 August 31 https://www.sql.kiwi/2012/08/deletes-that-split-pages-and-forwarded-ghosts.html