You must have thought how to store data in SQL Server? SQL Server is the platform, which provides a proper management of database. There is no doubt that it follows a strategy for the storage of all the files. All the users of SQL are aware with the fact that database objects are stored in MDF file. MDF files are responsible for the management of data in SQL Server. Therefore, in this article, we will learn the storage system of data in SQL Server data files.
Database Files and its Storage Structure in SQL Server
Database in SQL Server has two operating system files:
- Data Files
- Log File
Data Files
A data file consists of objects and data. Depending upon the needs, data files can be divided into two types:
Primary Database File
Each database has one primary data file. All the tables, database objects, indexes and views are stored in that file. The file extension of primary database file is .mdf.
Secondary Database File
This type is used at the time of exceeding the maximum allocated size for an individual file on Windows. It helps the database to increase in size. File extension for this file is .ndf.
Log File
Whenever users want to recover whole of the database in SQL Server, they require certain information, which are inside a log file. For one database there will be one log file and transactions of database are written on it even before to the data file. The file extension used by it is .ldf. LDF stands for Transactional Log file.
Inner Structure of Data File
Each database has its space to store the data, which is divided into different pages that are numbered from 0 to N. During the expansion of database files from its default size, those pages which are created newly are numbered from the last highest page number plus 1. In the similar way, at the time of shrinking of files it eliminates pages in decreasing order, which in the same manner starts from the highest page number. The basic unit of I/O for SQL Server operations is a page and each page is of 8 KB. A data file is a large array of pages. SQL Server stores different data with the help of multiple pages, Some of them are GAM, Index, IAM, Data, SGAM, and TextMix.
Page Addressing:
Now, if we analyze the pages addressing then we can see that every page in SQL Server is of equal size that is 8192 bytes. Each page has its own address, which is unique. It is a part of single database file. The ID of the file makes the first part of the unique address. Pages are numbered starting from 0. This is the mentioned format in which page address is written:
file number : page number
Data page is very essential among the page types. This is used for the storage of records to a database.
Structure of Data Page:
A data page contains three sections.
- Page Header
- Page Body
- Slot Array or Row offset
Page Header (0-95 bytes)
The first 96 bytes of a particular page is known as page header, which consists of the information of the page.
That is, Page ID, No of records in that page and IDs of previous/next pages.
Page Body
It is that part where actual data is stored. It is followed by the free space and slot array.
Slot Array
Slot array, also known as off-set array, is an array of two-byte values, which is read by the SQL Server in reverse i.e. from the very end of the page. Slot array keep slot points to a byte in the page wherever a record begins. First slot of the record slot array is saved in the last two bytes of the page that points to the page of the first record saved at the page.
Conclusion
One of the essential things that should be kept in mind is that, In SQL Server, the database file (.mdf) store its data as per 8 kb pages and it is important for SQL Server to sustain the sequence of data pages as per their header information. In case of mismatching of information or the data is not stored in sequential order into data pages, there is possibility in the corruption of database or you may face SQL database error like 5172, which is a header corruption error.