How SQL Server stores data in Data Pages – Part I

Hello all, in this article we’ll discuss the concept of pages in SQL Server and we’ll also check how our data gets stored in data pages.

We all know how to insert data into any table in any database, and we also did that. When we execute any query to insert data in any table, we know that the data is going to be stored in the data pages. But do you ever thought, how SQL server stores that data in those data pages?

Have you ever tried to open those data pages to check, how your data looks like in those data pages. What happens when you try to insert any data say 100 rows in a table and you data file can only take 60 rows, what about remaining 40 rows? How and where SQL server stores those 40 rows?

After entering records in data page, how much free space is available in data page?

If you don’t know those entire things, not to worry, keep calm and just be with me in this article.

So, before starting our journey, let’s create a couple of things with which we’re going to play, to understand above questions. So, we’ll need a database and a table.

First let’s create a database named ‘TESTING_DB’

CREATE DATABASE TESTING_DB

GO

After creating database we need a table, so we’ll create a table named ‘tblEmployees’.

USE TESTING_DB

GO

CREATE TABLE tblEmployees

(

     EmpId int primary key,

     EmpName char(500)

)

GO

We’ve created a table with 2 columns. I’m taking EmpName’s data type in char(500) i.e. in 500 bytes, just for example.

So, we’re done with our basic things. Let’s begin with the actual article.

We’ve our table and we’ll insert 1 record into it.

INSERT INTO tblEmployees VALUES (1,'Abhishek')

GO

When we execute above command, SQL Server immediately creates a new Data page and store the record into that.

SQL Server categorize data into 2 types i.e.

1. User Data
2. MetaData

SQL Server has different kinds of pages for different kind of data. For E.g. User defined data (i.e. above inserted data), will get stored into Data page, Index structure will get stored in Index pages, your page information, memory information, these kind of information get stored in IAM pages.

There are 14 types of page in SQL server. Please find below table for the same.

Page Type	Page Name	Description
1	Data Page	Details about how the data is actually stored inside the database data files
		Clustered index leaf level information.
2	Index Page	Use for Indexing purpose.
		Non clustered index leaf information.
3	Text Mixed Page	Used for small LOB values for multiple rows and for multiple types.
4	Text Page	Used for LOB value for single column.
7	Sort Page	Used for Sort operations on temporary pages.
		Mostly used in tempdb database.
8	GAM Page	Used to track allocation of extent.
9	SGAM Page	Used to track allocation of shared extent.
10	IAM Page	Contains page information, memory information.
11	PFS Page	Contains information of free space of pages.
13	Boot Page	Contain information about the page.
14	Server Configuration Page	Contains information returned from sp_configure.
15	File Header Page	Contains information about the file.
16	Differential Changed map	Contains the extents information in GAM interval that have changed since last full or differential backup.
17	Bulk Change Map	Contains the extents information in GAM interval that have modified by bulk operations since last backup.

In this article we’ll talk about 2 pages i.e. Data Page and IAM Page.

As we all know actual data gets stored in data page. IAM Page (Index Allocation Map), stores all the information about all SQL Server pages. So, in IAM Page you’ll get the number of pages used in your database.

If you want to see, what pages are used for your table in which you’ve stored your record? run below DBCC command.

DBCC IND('TESTING_DB',tblEmployees,-1)

GO

DBCC IND will take Database name, table name as parameter and if you want to get all the indexes including the pages you can put value as -1, it’ll display the pages with indexes.

After running the DBCC command, we got 2 rows, 1st rows is the Type 10 i.e. IAM Page as shown in above Page Type Table and 2nd row is Type 1 i.e. Data Page.

IAM PageID = 154

Data PageID = 153

So when we write any data in any table, first SQL server will check the IAM Page, from IAM Page it’ll find the Data page and then it’ll store the data in that table.

As we’re entering data in ‘tblEmployees’ table, SQL Server go to IAM Page and will check for the data page for that table.

And if you see in the above output, IAM PageID 154 is linked with Data PageID 153. So our record i.e. Employee Abhishek is stored in Data page 153.

Now let’s check where our record (Abhishek) is stored in the data page 153. For that run below DBCC command.

DBCC TRACEON(3604)

DBCC PAGE('TESTING_DB',1,153,1)

GO

If you’ve any doubt regarding why we passed 3604 as parameter in TRACEON, It’s nothing but a flag which gives output of DBCC command to the screen. If you run DBCC PAGE() without  DBCC TRACEON(3604), you’ll won’t get row details as shown in above output, you’ll only get a message as shown below.

I hope you all understand the reason of TRACEON(3604). Let’s move further.

So, let’s execute below statement to see the content in our Data Page.

DBCC TRACEON(3604)

DBCC PAGE('TESTING_DB',1,153,1)

GO

After running the statement, we’ll get below details which is the content of Data Page.

So this is the content of Data Page.

we all know, a page is divided into 3 sections.**    

1.     ** Page Header.
2.      Actual Data.
**3.    **

Offset Table.

Page Header: This gives you the details about the type of page, next page and previous page id, free space, etc. as marked in the below image.

Actual Data : Actual Data which we insert in our object is stored in this section. If you remember, we had inserted 1 record employee named ‘Abhishek’, that record will be saved here, in this section as shown below.

If you see in the preceding output, Record Type = PRIMARY_RECORD, which means it’s our actual data.

After this section, our last section which is Row Offset.

Offset Table: This section of Data file actually tells you, where exactly record Abhishek is saved.

If you see the Row Offset, it’s pointing the Actual data’s location.

So, I think, till now we’ve understand how and where our actual data stored in data pages.

In this part-I article, we've seen how to view data pages and types of pages used in SQL Server. We’ve also seen that how our data look in data page. But this is not the end of our article, this is just the half part. In my next part of this article, I’ll discuss how bytes are allocate for each section and for each record . What happens if byte of records exceeds the byte of data pages. We’ll learn these things in next article "How SQL Server stores data in Data Pages – Part II".