Hi,
I heard that there will be a page-split whenever there is no space to accomdate the data in the index pages. Since i'm beginner to the topic index. I would like to know how page-splits happens w.r.t clustered and non-clustered.
Assume that my (Clustered)index page look like this.
2
4
6
8
When i try to insert a row '5' it moves half of the page to another page as the index page is full.

But, what is the case in Non-clustered index whether there would be page-split because the non-clustered is basically a logical grouping of data so even when i insert the value '5' it moves to another page automatically without disturbing the existing data in the index page unlike clustered behaves (physical sorting - 6 and 8 are moved to another page).

My question here is will there be a pagesplit in case of nonclustered index?

Thanks,
Chandra

__________________
Thanks,
Chandra

No... Non-clustered indexes make a new extent and all 8 pages are dedicated to the table which is why they also call it a "uniform extent". Non-clustered indexes don't "split" like clustered indexes do.

--Jeff Moden

It also depends in the FILLFACTOR setting. The split will occur when page has enough data.

In BOL, they defined Page splits as moving approxmately half of the data on one side and other half on other side. Is it always the same case or it behaves differently when the data to be inserted at the end of the page.

Thanks,
Chandra