How SQL Server Stores Data in Data Pages: Part 1

Hello all, in this article we'll discuss the concept of pages in SQL Server and we'll also check how data is stored in Data Pages.

We all know how to insert data into a table in a database and we also do that. When we execute a query to insert data into a table, we know that the data will be stored in the Data Pages. But do you ever think, how does SQL Server store that data in those Data Pages?

Have you ever tried to open those Data Pages to check, how your data looks in those Data Pages? What happens when you try to insert data, say 100 rows in a table and your data file can only take 60 rows, what about the remaining 40 rows? How and where does SQL Server store those 40 rows?

After entering records into a Data Page, how much free space is available in the Data Page?

If you don't know the answer to all of that, not to worry, keep calm and just remain 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 the preceding 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 the 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 just now created a table with 2 columns. I'm taking the EmpName's data type in char(500), in other words in 500 bytes, just for example.



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

We have our table and we'll insert 1 record into it.

INSERT INTO tblEmployees VALUES (1,'Abhishek')
GO

When we execute the preceding command, SQL Server immediately creates a new Data Page and stores the record into that.

SQL Server categorize data into the following 2 types:

1. User Data.
2. Metadata.

SQL Server has various kinds of pages for different kind of data. For example, user defined data (in other words the preceding inserted data) will be stored in a Data Page, an Index structure will be stored in Index pages and your page information, memory information and those kinds of information are stored in IAM pages.

There are 14 types of pages in SQL Server. Please find the following table for that.

In this article we'll talk about the 2 types pages, Data Page and IAM Page.

As we all know, the actual data is stored in Data Pages. An Index Allocation Map (IAM) Page stores all the information about all the SQL Server pages. So, in an IAM Page you'll get the number of pages used in your database.

Do you want to see what pages are used for your table in which you've stored your record? Run the following DBCC command.

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

DBCC IND will take a database name and table name as parameter and if you want to get all the indexes, including the pages, then you can provide the value as -1, it'll display the pages with indexes.



After running the DBCC command, we got 2 rows, the first row is a Type 10, in other words an IAM Page, as shown in the preceding Page Type Table and the second row is Type 1, in other words a Data Page.

IAM PageID = 154
Data PageID = 153

So when we write data into a table, first SQL Server will check the IAM Page then from the IAM Page it'll find the Data Page and then it'll store the data in that table.

Since we're entering data into the "tblEmployees" table, SQL Server first goes to the IAM Page and checks for the Data Page for that table.

And if you see in the preceding output, the IAM PageID 154 is linked with Data PageID 153. So our record, 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 the following 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 that gives output of the DBCC command to the screen. If you run DBCC PAGE() without DBCC TRACEON(3604), you'll won't get row details as shown in the preceding 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 the following 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 the following details that is the content of Data Page.

 

So we've got our contents of the Data Page.

Now, as we all know, a page is divided into the following 3 sections.

1. Page Header
2. Actual Data
3. Offset Table
   

Page Header: This gives you the details of the type of page, nextpage and previous page id, free space and so on as marked in the following image.

Actual Data: The Actual Data that we insert into our object is stored in this section. If you remember, we inserted 1 record with 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 is Row Offset.

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

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



So, I think, until now we've understood how and where our actual data is 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 how our data looks in Data Page. But this is not the end of our articles, this is just half of them. In my next part of this article series, I'll explain how bytes are allocated for each section and for each record. What happens if a byte of records exceeds the bytes allocated for Data Pages? We'll learn these things in the next article.

Next Part: How SQL Server Stores Data in Data Pages: Part 2