Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 7th, 2008, 02:34 AM
Authorized User
 
Join Date: Dec 2006
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default Page splits - Clustered vs Non-Clustered Index

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
 
Old August 8th, 2008, 06:21 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old August 10th, 2008, 05:11 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old October 20th, 2008, 04:23 AM
Authorized User
 
Join Date: Dec 2006
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Clustered Index Scan bmains SQL Server 2000 5 June 7th, 2010 11:35 AM
Clustered PK Constr fails to create on big table kapluni SQL Server 2000 5 November 23rd, 2005 06:27 PM
Question about clustered indexes patwadd SQL Server 2000 1 July 15th, 2005 05:01 AM
Page index error lily611 General .NET 7 August 31st, 2004 11:19 AM
Size of Clustered Index MikeSchnell SQL Server 2000 0 May 7th, 2004 02:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.