Clustered Index Pages

Hi all,
I'm confused with the following theoretical scenario... ...consider if you
have a table made of 1000 pages with a clustered index on AuthorID. If page
1
contains data with AuthorID's "Aa" to "An" and after a series of inserts is
100% full, if a row with AuthorID "Ab" is inserted would the page split with
the original page staying as page 1 with a forwarded records pointer to a ne
w
page at page number 1001?On Mon, 17 Apr 2006 13:32:16 -0700, BenUK wrote:

>Hi all,
>I'm confused with the following theoretical scenario... ...consider if you
>have a table made of 1000 pages with a clustered index on AuthorID. If page
1
>contains data with AuthorID's "Aa" to "An" and after a series of inserts is
>100% full, if a row with AuthorID "Ab" is inserted would the page split wit
h
>the original page staying as page 1 with a forwarded records pointer to a n
ew
>page at page number 1001?
Hi BenUK,
The data pages of a clustered index are not allocated in a consecutive
order. They may be spread out inside the DB files. Hence, the notion of
a "page 1", "page 2", ..., "page 1000" is only logical, not physical.
If SQL Server searches a specific entry in a clustered (or actualy in
ANY index) index, it starts at the root page (each index has exactly one
root), goes straight to the intermediate page at the next level and so
on until it gets at the leaf page (usually 2 to 4 steps).
If SQL Server has to scan an index, it uses the pointers to the next and
previous page that are stored on each leaf page. These pointers link the
pages together in a sequential order without the need to store them in a
physically sequential way.
So what happpens if a page in a clustered index overflows is this:
* A new page is allocated, somewhere in the available storage space;
* Approximately half of the existing data of the full page is moved to
the new page;
* The next page/prev page pointers on various leaf pages are updated to
ensure that the new page is inserted in the right position in the
logical chain. This makes the new page "page #2", the previous page 2 is
now page 3, etc - without having to reposition any of those existing
pages;
* A new row with a pointer to the new page is added to the intermediate
page that points to the page that has overflowed.
Forwarding pointers are only used for tables with no clustered index
(heaps), with at least one varying length column in the data. If an
update causes the varying length portion of the data to grow and the
page has not enough room left for the growth, the row is moved to a new
location; the data at the old location is then replaced by a pointer to
the new location (the "forwarding pointer"). This is done to save the
overhead of hunting down the row in all nonclustered index (in the
absence of a clustered index, they point to the data using the physical
storage location).
If the size of the row grows again at a later time and the storage of
the row has to move anoother time, the original forwarding pointer is
updated with the new location - that is, there will never be a chain of
forwarding pointers.
Hugo Kornelis, SQL Server MVP