Data page in sql server

SQL servers store the data records in data pages. A data page consists of three sections. Page Header, actual data and row offset array. Data rows with all data, except text, next, image, nvarchar (max), varchar (max), varbinary (max), and xml data. The actual data in your table is stored in Pages, except BLOB data. If a column contains BLOB data then a 16 byte pointer is used to reference the BLOB page. The Page is the smallest unit of data storage in Microsoft SQL Server. A page contains the data in the rows. A row can only reside in one page. Each Page can contain 8KB of information, due to this; the maximum size of a Row is 8KB. A group of 8 adjacent pages is called an extent. A heap is a collection of data pages.

Let’s see how the Data pages

Here I am creating a table and inserting some records.

create table employee ( EmpID int,   Name varchar(100),   EAdd varchar(1500),   Mobile varchar(10) )

Now I am inserting a record.

  

Let’s see where the data store in the pages. For seeing this we need to use of DBCC command.

First we see the index allocation page using below script.

DBCC IND('Test',employee,-1) --Test – database name --employee –table name -- -1 it means see the all pages stored in IAM(Index allocation Map) page.

   

Now I am seeing the page vales using below script

DBCC TRACEON(3604) DBCC Page('Test',4,9,1)

Executing this command

 

Basically page is divided on 3 parts

Ø  Page header (It is 96 bytes and stores the Meta data about page like page number, page type, the amount of free space, allocation unit ID.)

Ø  Data rows (It store the actual data)

Ø  Row offset (It is 36 bytes, stores the information about the data rows and address to next pages)

I this given example we can see that after inserting one record 8055 byte space is free

I means for one record take

For one record take 39 bytes. Let’s see I am inserting some more records

Now there are 16 records in a table. New see the free space on the page

  

See in the chart

  

 For one record it takes 39 byes. Now think suppose we insert 208 records then what happen.

  

8094 byte store in 1 page and other 330 byte store in 2^nd page.

See two pages created.

  

188 records are store on the page 1 and remaining 28 records are store in 2^nd page.