SQL Server physically stores data on disk structures in data pages and extents. When a database is created in SQL Server a primary data file with a .mdf extension and a Transaction Log file with a .ldf extension will be created. Optionally, secondary Data files could also be created with a .ndf extension. For this post, we will focus on the data files while transaction logs will be discussed in a future blog.

Data files are given logical names like AdventureWorks.mdf, however, data records or rows are physically stored in 8kb data pages on the hard drive. Eight contiguous data pages are called an extent. For those that need help with math, each extent contains 64kb of data. There are two types of extents possible. Uniform extents are where all eight pages in the extent belong to the same object, while Mixed extents will have data pages from different objects.

Each data page is 8,192 bytes with 8060 bytes being used for data or free space. The first 96 bytes are used for the page header that contains information like the Page Number and Page Type. Since many data rows could have variable-length columns, the remaining 36 bytes is used for the Row Offset Array which is used to determine where each data row ends within the data page.

Data rows from a table are stored in the data pages in either an unsorted order (Heap) or a sorted order (Clustered Index). If the data has not been provided a clustering key, the data would be unsorted, and the data structure would be considered a Heap. If a clustering key is provided, the data rows will be sorted by that key and would be considered a Clustered Index.