SQL Server filegroups and partitions

In one of the most streamlined SQL Server databases, two operating system files are included: Data files and log files.

Data files contain data and some objects, such as tables, indexes, stored procedures, views, etc.;

The log file contains information for recovering a database transaction.

Database files

There are three types of files in the SQL Server database.

Primary files, each database has a primary file that uses the. mdf extension.

Secondary files, secondary files are optional, user-defined. Secondary files can be distributed across different disks, different hard disks, which can be used to break the system's limits on file size, or to promote I/O, using the. ndf extension.

Transaction log files, each database has at least one log file, and the saved log information is used to recover the database using the. ldf extension.

For example, a simple database can be created to contain a primary file and a log file, all the data and objects are placed in the main file; a slightly more complex database can create a primary file, five secondary files, all the data and objects distributed among the six files, And the transaction information is logged by multiple log files.

By default, the data and logs are stored on the same drive in the same path, which is used to correspond to the single-drive system. However, in a production environment, this is not the optimal choice, it is recommended to put the data and logs on different hard disks.

SQL Server filegroups and partitions