4.2.4 SQL Server database files

4.2.4 SQL Server database files
SQL Server stores the data in a data file and stores the transaction records in a transaction-log file. If you assemble them together with a logical database name, the files are the databases. A SQL Server database can have multiple data files and multiple transaction log files (although a transaction log file is usually sufficient).
When the database is first created, it will have a master data file with a default file name extension of. mdf . The database can also have a secondary data file with a default extension of M df. These data files can be grouped into a logical group called filegroups, which is described in the 5th chapter. The database has at least one transaction log file with a default extension file name of. LDF . The file name extension of the SQL Server database is not mandatory, so you can use any extension, but we usually use the default extension because the default extension can easily show the purpose of the file. The following sections describe only
The physical storage structure of the data files and transaction log files. To fully understand the database creation process and how to create and make
Refer to chapter 5th for a document.

The primary data file (. mdf) of the

4.2.5 data file
database and any secondary data files (. ndf) have the same structure. Both of these files are used to store data and to allow SQL Server to efficiently locate, read, modify, and add data to the database metadata.
1. The Zone
area is a file storage structure for SQL Server with a size of 64KB. The area is comprised of 8 contiguous 8KB pages. There are two types of zones: mixed zone and unified zone. A blend zone contains pages from multiple objects. For example, a mixed area might contain a
Data page from table A, an index page from table B, and more data from table C. Because there are 8
pages in a zone, 8 different objects can share a single area. The unified zone contains 8 contiguous pages that belong to the same object. It
is shown in the difference 4-1.
when retrieving data or writing data to disk during a database operation, the zone is the basic structure of data retrieval. SQL
Server always allocates space in increments of 64KB. This corresponds well to how the data is organized in memory and in the NT File System (NTFS) format partition. However, as mentioned earlier, SQL Server can store pages from different objects in a single region to maximize storage efficiency.

2. Page
All data and metadata for the SQL servei* 2008 database are stored in the page. Unlike zones, pages always store data from the same object. This includes rows from the table, rows from the index, and large object data. The size of the page is
8KB, and is organized in a zone of 64KB (consisting of 8 consecutive 8KB pages). Each page has a 96-byte header,
It contains information about the page, such as the page number, the type of data stored on the page, the available space on the page, and the
Object. SQL Server contains a number of different types of pages for storing and managing data.
Data page
The data page contains data rows from the table. These rows cannot be spread across pages. Because of the page header and line offset information, the maximum
The row size is limited to 8060 bytes. The row size is determined by the number of columns in the row and by the type of data defined on each column . For
To maximize performance, the table and index rows should be as narrow as possible. For example, if the width of a table row is 4 100
Bytes, which can only store one row per data page, leaving nearly 4000 bytes of unusable space. From such a structure
Table, retrieving only 4100 bytes of data requires reading 8KB of data. Efficiency is obviously too low. Extract
Data page Structure 4-2.

For each row on the page, each row offset block occupies 2 bytes of space. In order to optimize storage space, the table
The physical arrangement of rows differs from their logical definition. When a row is stored on a data page, it is used with a 4 word
The section's header, which uniquely identifies the row in the page, is identified. The header is followed by a fixed-length data column, a null block, a
Variable length block, and all variable-length data columns at the end of the physical line, as shown in 4-3

A null block contains a 2-byte block that indicates how many columns in the row can contain null, followed by a null
Whether the column is an empty bitmap. The size of the null bitmap is 1 bits per column, rounded to the nearest number of bytes. 1? 8 Nullable
The column requires a 1-byte bitmap. 9? 16 columns require a 2-byte bitmap, and so on.
As with the null block,variable length block contains a 2-byte block, indicating how many variable lengths can be included;followed by a table
maximum length of each variable length column .。 Unlike a null block, the size of a variable-length block bitmap is 2 characters per column
section, which points to the end of each variable-length column so that all variable-length data can be stored consecutively at the end of the data row. If not
If a column is defined as a variable-length column, the variable-length block will be slightly smaller.
Index page
The index page contains rows from the index. They have the same structure and limitations as data pages.
Text/image page
When you define a column with a large object data type, SQL Server places a 16-byte pointer over the actual data row and places the large object data on a separate data page. This type of data includes defined as text, ntext, image,
varchar (max), nvarchar (max), and varbinary (max) and XML data o
Global Allocation Mapping Table (GAM) and secondary Global Allocation Mapping table (SGAM) pages
The GAM and SGAM pages are allocation pages based on each file management area. The 2nd page of each data file is GAM
Page, and the 3rd page is the Sgam page. SQL Server adds additional GAM and SGAM pages if necessary, because each GAM and Sgam page can only track 63 904 extents. The GAM and SGAM pages make up an indication that the area is unified
The bitmap of the mixed area. The GAM and Sgam bitmaps also indicate that the area is full, empty, or that there are available numbers
Data Sheet.
Page free space (PFS) page
The PFS page records the status of each page, whether the page has been assigned, and the available space on each page.
Index allocation Map (IAM) page
The IAM page contains information about the extents used by the table or index. It contains the location information of the 8 initial pages of an object,
And a bitmap that represents the extents used for the object. Each IAM page can track up to 512 000 pages of data. Sql
The server uses IAM and PFS pages to find and assign new pages to the data.
Bulk Change mapping table (BCM) page
The BCM page contains the location of the extents modified by the human capacity operation since the last transaction log backup. Large-Capacity Operation package

4.2.4 SQL Server database files