How SQL Server Stores Data in Data Pages: Part 1

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.

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.

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.

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