Entire Data file is divided into 8kb physical chunks called pages

Why we are dividing this files into pages à for easy of organizing the data.

Group of 8 consecutive pages is called extent

What is in the page

Page is consists page header

And it consists of location of page, address, and owner, pointer to previous page or next page

Extent is logical grouping of the pages.

Page fragmentation:

If the pages of the table are located at different places, then it will cause the page level fragmentations

One page should be allocated to particular object only. A single page cannot store the information of 2 tables. If you have table 1(t1) data in 3 pages and last 3 page is not full, then you have created the table 2(t2) then it will use new page, not the 3rd page which is free and of table 1(t1). Single object can resides on different pages.

This page and extent management of sql server is different from other rdbms products but expect from Sybase. As the roots of sql server is from Sybase only.

We have 2 types of fragmentation

1. Inter page fragmentation : when a data is deleted from the page

2. Across pages fragmentation: when the pages of table is spitted across different locations

Important point: A Row cannot be span over multiple pages

How we can eliminate this limitation

Sql server will store only 1023 columns and another 1 column is used for replication

If the row is >7.1 kb, then the solution is below one

Other than integer, character data we have image, textual, xml which will not use the page concept

Due to this fragmentation will occur, but the space that will left blank will be used for future purpose, if the row 1 is updated with some more data, then it will use that space.

This is high level explanation of how the data is organized.

We will see the internals of pages, records in next article.

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512