SQL SERVER files and file groups

1. Definitions and relationships of files and file groups

Each database has a primary data file and several slave files.Files are physical representations of databases. File groups can include files distributed in multiple logical partitions to achieve load balancing.. File groups allow you to group files for management and data distribution/placement. For example, you can create three files (Data1.ndf, Data2.ndf, and Data3.ndf) on the three hard drive and assign these files to the file group fgroup1. Then, you can explicitly create a table in the file group fgroup1. The query of data in the table is distributed to three disks, thus improving the performance. Creating a single file in a RAID (redundant disk array) Strip set can also achieve the same performance improvement. However, files and file groups allow you to easily add new files to a new disk. In addition, if the size of a database exceeds the maximum size of a single Microsoft Windows file, secondary data files can be used to allow the database to continue to grow.

2. Common Problems of files and file groups in practical applications

Generally, the database we construct has only two files, mdf and ldf. However, there are two disadvantages:

(1) files may be too large.

We know that the mdf file is a database file, which means that the mdf will increase accordingly as the database increases. Obviously, data expansion is too common in current applications, when your application grows, the mdf file also grows. However, windows requires a file size, in this case, it is easy to cause the mdf file to reach the limit of the file size allowed by windows (so the database crashes ).

(2) The disk array is not used.

A lot of large servers have disk arrays. You can make a disk array look like n Rotating Disks, the disk array is designed to achieve greater read/write efficiency through the concatenation of multiple disks. however, if your database only has one mdf file (ldf file is not considered for the time being), you can only use one disk in the disk array. in that case, the efficiency of the expensive disk array will change from parallel to series. imagine if we can distribute mdf into multiple files, for example, allocate a file to each disk on the disk array, and then distribute the mdf data to each file, when I read the data, I connected the data in series to make full use of the access efficiency of the disk array.

We have never encountered these two problems (the conditions are not met), but they are almost fatal when developing large services.

3. Query file groups and file statements

In 2005, run

SELECT df. [name], df. physical_name, df. [size], df. growth, f. [name] [filegroup], f. is_default FROM sys. database_files df JOIN sys. filegroups f ON df. data_space_id = f. data_space_id

4. MSDN official explanation

Understand files and file groups

Each SQL Server database has at least two operating system files: one data file and one log file. A data file contains data and objects, such as tables, indexes, stored procedures, and views. The log file contains the information required to restore all transactions in the database. To facilitate allocation and management, you can set up data files and put them in a file group.

Database Files

The SQL Server database has three types of files, as shown in the following table:

File	Description
Main	The main data file contains the database startup information and points to other files in the database. User Data and objects can be stored in this file, or in secondary data files. Each database has a primary data file. The recommended file extension for main data files is. mdf.
Secondary	Secondary data files are optional and user-defined and stored. By placing each file on a different disk drive, secondary files can be used to distribute data to multiple disks. In addition, if the database exceeds the maximum size of a Single Windows file, you can use secondary data files so that the database can continue to grow. The recommended file extension for secondary data files is. ndf.
Transaction Log	The transaction log file stores the log information used to restore the database. Each database must have at least one log file. The recommended file extension for transaction logs is. ldf.

For example, you can create a simple databaseSalesIncluding a main file containing all data and objects and a log file containing transaction log information. You can also create a more complex database.OrdersWhich includes one primary file and five secondary files. The data and objects in the database are scattered in all six files, and the four log files contain the transaction log information.

By default, data and transaction logs are stored in the same path on the same drive. This is the method used to process a single disk system. However, in the production environment, this may not be the best method. We recommend that you store data and log files on different disks.

File Group

Each database has a primary file group. This file group contains primary data files and all secondary files that are not placed in other file groups. You can create a user-defined file group to set up data files for management, data allocation, and placement.

For example, you can create three files Data1.ndf, Data2.ndf, and Data3.ndf on the three disk drives, and then assign them to the file group.Fgroup1. Then, you can explicitlyFgroup1Create a table. The query of data in the table is distributed to three disks, thus improving the performance. The same performance improvement can be achieved by using a single file created in a RAID (Redundant Array of Independent Disks) Strip set. However, files and file groups allow you to easily add new files to a new disk.

The following table lists all data files stored in a file group.

File Group	Description
Main	A file group that contains the main files. All system tables are allocated to the main file group.
User-Defined	Any file group that is explicitly created when you create a database for the first time or modify a database later.

Default file group

If no file group is specified when an object is created in the database, the object is assigned to the default file group. At any time, only one file group can be specified as the default file group. By default, files in the file group must be large enough to accommodate all new objects not allocated to other file groups.

The PRIMARY file group is the default file group, unless changed using the alter database statement. However, the system objects and tables are still assigned to the PRIMARY file group, rather than the new default file group.