Microsoft SQL Server Database File Structure (SQL Server 2008 R2)
Note: this article originally appeared as article #46 on ugts.org. I have since moved it to Blogger since it was the only resource that most people wanted from the website. A database data file (MDF or NDF) in SQL Server is stored using the format defined by the SQL Server Storage Engine. Database files use a moderately simple format with a small set of data structures to store all the objects in an SQL Server database. A database file at its simplest level is an array of 8KB pages - bytes 0x0-0x1FFF in the file belong to page ID = 0. Bytes 0x2000-0x3FFF belong to page ID = 1, etc... Page ID values start at 0 and count upward. Page IDs are 32 bit signed values, meaning that the largest possible database file in SQL server is 2^31 pages * 2^13 bytes per page = 2^44 bytes = 16TB. This is a limitation that has been present from SQL Server 2000 up to 2008 R2. Pages are the fundamental unit of allocation for data stored to disk. Every obje...