How is data physically stored in a database: All you need to know

In the article we’ll discuss “How is data physically stored in a database” in the form of “series of 8kb pages”. We all know that SQL server stores data in tables, if we think next step where table’s data internally get stored then SQL pages come into picture.

Yes, MS SQL data internally get stored in the form of pages and each page is of 8kb. So, when you insert any data into a SQL Server database, it saves the data to a “series of 8 KB pages” inside the data file. When data of any column increases then next data is saved on next new page and these pages are internally referenced to each other.

Below is the example of page which contains three main sections as:

• Page header
• Data Row
• Row Offset  

Page header: Given the information about the page and also how many chunks are available. It has 96 bytes it store all this information.

Data Row: These are the data rows in which data is stored. Total bytes available for data storage are 8060 bytes.

Row Offset: It keep the record of “memory address” where actually data is stored.  It also stores the reference of other pages too. (In case of large data multiple pages are created). Total bytes available for storing row offset information are 36 bytes.

Let’s check out video “How is data physically stored in a database”

Now in next section we’ll insert data into table and practically see the data on SQL pages. We’ll use DBCC command to get the required information. Let’s check out below:

Below DBCC command will show the path the “mdf” file created for storing the database  

   DBCC ShowFileStats

Insert some data in your SQL table and run the following command in SQL

 DBCC IND('TestDb', 'Students',-1)

DBCC stand for “Data Base Consistency Command”, IND is for index and it will accept 3 parameters, first is DB name and second one is table name which is Student, third parameter is -1 or -2 for different level of details.

On running the above query, you will get above shown details. Focus on “PagePID” and “PageType”. “PageType” =10 means header whereas “PageType” =1 is the data page. Now with this we have identified that page with id 310 is data page and 311 is header page.

Let’s find “How is data physically stored in a database” and also see its address locations

Now in next step we will see the data stored in page with ID= 310 as we have discussed above. You can also check out above video for more detailed explanation.

In order to see the details you have run first command which will on the trace. When you will run this query it will give error but no worries trace will on.    

DBCC traceon(3604)  -- it is used to on Trace
DBCC page('TestDB',1,310,1)

 Now we’ll run the second query given above which is used to get page level information, first parameter is DB name, second parameter indicate level of page info, third is page ID whose information we need, fourth parameter is level of details. Its value varies from 0 to 4 as per need, 0 give only header and 1 give information for all i.e., header, offset and data page. Following is the output of above query:

In above screen we can see the page id and header as pointer out using arrow. In below screen page Offset information of memory address and also the data which we have stored.  

Let’s get memory address below, which will explain how data is physically stored in a database in form of 8kb pages.

I this article we have explained (with example) the concept of SQL server pages, its header, rows, and Offset footer, etc. We have also explained how pages link with each other, what information header footer has in it. Hope this will work as add on to your knowledge. For additional knowledge of “How is data physically stored in a database” you can refer HERE

For more knowledge of MS SQL, you can check this section HERE

You may also like:

• SQL server Column Encryption using symmetric key.
• How to Update using Select statement in SQL Server.
• SQL Server Interview Questions and Answers.
• Import One Database to another in SQL server.
• Generate database scripts in SQL server.
• Different ways to search SQL objects in database.
• SQL CASE statement
• SQL IIF function
• QUOTENAME function in SQL
• How to Take SQL Server Database Offline/Online
• How to use SQL ORDER BY in Your Queries
• SQL Paging using OFFSET and FETCH