SQL Server Data Components – How Data is Stored

by Manzoor Siddiqui · Published July 18, 2015 · Updated September 24, 2015

SQL Server Database is a combination of different data files and components as listed below. Here we will see how data is stored in database and we will discuss it’s components.

Basically there are three types of files which completes a database.

1. Primary data file (also called Master Data File or .mdf)

2. Secondary data files (also called Non-primary Data File or .ndf)

3. Transaction Log file (also called Log Data File or .ldf)

Primary Data File

When you create a database, at that time primary data file is created. This primary data file is used during start-up of the database and is linked to other files. One primary data file is associated with only one database which contains database objects like tables, stored procedures, functions, views and indexes etc. Default extension for primary data file is .mdf but if you want you can choose your own file extension also during database creation step because .mdf extension is not compulsory but it is recommended for primary data file.

Secondary Data File

Secondary data file is similar to master data file which also contains database objects like tables, stored procedures functions, views and indexes etc. but this data file is optional and it is created by users when one data file is full. One can also create secondary data file to boost the performance by spreading data files across different disk drives. It is also called non-primary data file and recommended file extension is .ndf but you can choose your own file extension also.

Transaction Log File

Transaction log file is required to recover the database at the time of failure and to minimise the data loss. With every database, one log file is associated which keeps record of all transactions. Log files are very important and used in high availability and disaster recovery scenarios like log shipping, replication and database mirroring. When you perform any data manipulation operations, data pages are fetched into memory and it is modified as per query requests. In this operation some dirty pages are created which are written to transaction log and during background process which is called checksum, the dirty pages are written to database files.

Other data files

Whatever data is available in database, it is written on the file pages of 8KB size. This 8KB is shared between header information and data. These pages are sequentially arranged and to process these pages, database engine requires page number and file ID associated with each page. Each page keeps related single database objects only. When 8 contiguous pages are groups together it is called extent. This extent is used to manage and organise data pages in database.