Looking online I have found a lot of information describing how pages work, what they are used for, and how they tie into the architecture of MSSQL Server. I understand they are a part of the mdf file created to store the data itself, however what I'd like to know is how are pages stored on disk. Is each page (or extent) itself a file on disk, or is it just data contained a giant mdf file?
Reading this it seems like that latter is true, but it is not completely clear.
Pages in SQL Server are basically logical concept designed to store data and access it.
Quoting from Wikipedia and similar StackOverflow Thread.
Storage space allocated to a database is divided into sequentially numbered pages, each 8 KB in size. A page is the basic unit of I/O for SQL Server operations. A page is marked with a 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. Page type defines the data contained in the page: data stored in the database, index, allocation map which holds information about how pages are allocated to tables and indexes, change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text. While page is the basic unit of an I/O operation, space is actually managed in terms of an extent which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains varchar or varbinary data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an allocation unit) and replaced with a pointer to the data.[21]
