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.
The page consists of 3 sections
- Page Header
- Actual Data
- Row offset array
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).
Types of Pages
- Data Page – stores data entered by user.
- Index Page – Indexes are pointer which store address of original pages for quickly locating data
- Free space page – It stores page allocation information and unused space available on pages.
- Text/Image – It stores large object data (LOB) like Text, Image and XML Data.
- GAM (Global Allocation Map) or SGAM (Shared Global Allocation Map) – It stores extent allocation information.
- BCM (Bulk Changed Map) – Stores extents information in a Bulk Operation
- DCM (Differential Change Map) – It stores modified extents information after Full BackUp.
- I AM (Index Allocation Map) – Stores extents information that are used by a table (or) Index.
- These are important types of pages. All these pages are further grouped into an Extent.
SQL Server Extents
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
- Uniform Extent: If all pages are going to store same table data
- Mixed Extent: If the pages shared by 2 (or) more tables.
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.
SQL Server File
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 (Master Data File)
- LDF (Log Data File)
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
Below topics further included in this series
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server