[Translated] How Data Is Stored in SQL Databases
This article provides an in-depth analysis of the physical data storage mechanisms in SQL Server. It highlights that while data logically exists in rows and columns, it is physically stored using 8KB data pages as the basic unit. When a table has a primary key, SQL Server creates a clustered index by default, ensuring that data is physically ordered by the primary key. This storage structure takes the form of a B-tree (also known as the clustered index structure), composed of root nodes, intermediate levels, and leaf nodes. The leaf nodes directly store the actual data rows, whereas the root nodes and intermediate levels contain index rows that include key values and pointers to lower-level nodes. Through this hierarchical pointer structure, the database engine can efficiently locate and retrieve data. Understanding this underlying structure is crucial for developers, particularly when performing SQL Server performance tuning, troubleshooting, and optimizing slow queries. Mastering the operational principles of data pages, B-trees, and index rows forms the foundation for enhancing database performance.