In an SQL server all the data will be stored in the form of records, these records also called row data. All these records further grouped into a page. The page is a default storage unit of the SQL server. The size of the page is 8kb.
Page Header – It consists of Page ID, Page Type, Object ID Header version.
Page ID – To identify a particular page using a unique page ID.
Page Type – What type of page it is either data page or Index page.
In Row the offset location of the record will be stored (2 bytes).
Extent is a storage structure consists of 8 consecutive SQL Server pages. Pages in a Extent can be one table (or) upto Eight tables.
There are 2 types of Extents
When a table is created and a row is inserted table gets 1 page in mixed extent, when a table grows then these tables moved to a uniform extent. This is to manage space efficiently.
All the extents further group into a File. A file we will have better control in SQL Server.
There are 2 types of files mainly,
MDF – Stores Permanent Data
LDF – Stores changes information will be recorded later these changes apply on MDF Data.
Files combine to form the database. We require a minimum 2 files 1 MF and 1 LDF to create a database. Maximum we can ‘n’ number of files means No limit.
Some files stored system data and some store user database data. Logically dividing databases into groups called File Groups.
Records > Pages > Extents > Files > Database
#Ref. Ram Mohan & Rama Krishna notes
[…] 3. SQL Server Storage Architecture […]
[…] SQL Server Storage Architecture […]