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.

What is a Data Page?

Data files are given logical names like AdventureWorks.mdf, however, data records are physically stored in 8kb data pages on the hard drive. Eight contiguous data pages are called an extent.

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. Mixed extents will have data pages from different objects.

What is the Size of a Data Page?

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. This Page header 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. This is used to determine where each data row ends within the data page.

Data rows from a table are stored in the data pages as a Heap or Clustered Index. If the data is unsorted, it is a Heap. If it is in sorted order, it is a Clustered Index.

When the data has not been provided a clustering key, the data would be unsorted. This data structure would be considered a Heap. If a clustering key is provided, the data rows will be sorted by that key. This data structure would be considered a Clustered Index. Learn more about Table Structures in SQL Server.