How is Data Stored in SQL Database

Introduction

Being Software Developer / DBA / Database Developer, its important to know or understand the concept How the Data is Stored in SQL Database. This knowledge is very important when we want to troubleshoot and fix SQL queries that are not performing well from the Tuning point. In this article, we will discuss how the data are stored in SQL Database.

How Data Stored in SQL Database

We already know the data in the tables are stored in rows and columns format, but physically data are stored as Data Pages. Data Page is the fundamental unit of data storage in SQL Server and its size is 8 KB. Whenever any data is inserted into SQL Server Database tables, is saves that data to a series of 8 KB data pages.

Table data in SQL Server is actually stored in a tree like structure. Take any table as an example to understand this concept,

• The table should have a Primary Key column, so by default a clustered index column is created.
• The data that are physically stored in the database are sorted by the Primary Key column values.
• Data are stored in a series of data pages in a tree like structure.

• The nodes at the bottom of the tree are called data pages or leaf nodes of the tree.
• The number of rows that are stored in each data page depends on the size of each row. The Size of single data page is 8 KB.
• The node at the top of the tree is called Root Node.
• The nodes between the root node and the leaf nodes are called intermediate levels. There can be multiple intermediate levels.
• The root and and the intermediate level nodes contain index rows, and the leaf nodes contain the actual data rows.
• Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf node.
• Tree like structure has a series of pointers that helps the database engine find the data quickly.

Conclusion

In this article, we have discussed how the data in the SQL Server Database are physically store. I hope this article gave broader knowledge, please share your feedback in the comment section.