SQL Server filegroups and files

1, the role of filegroups and files

SQL Server data is stored in a file, which is the physical entity that actually stores the data, the filegroup is a logical object, and SQL Server manages the file through a filegroup.

A database has one or more filegroup,primaryfilegroup is required, and the user can add filegroup as needed. Each filegroup manages one or more files, where Primaryfilegroup manages a master data file with an. mdf extension, which is generated by default; files with the extension. NDF are added by the user as needed.

The filegroup manages the files on the disk, and the files contain the actual data of SQL SERVER database.

2, Example DB

The filegroup of the 2.1 example DB is as follows

Primary is a filegroup and is a default filegroup. If no filegroup is specified in the Create Table,create index, the actual data is stored using the default filegroup managed file.

2, each filegroup has a corresponding file,file is the physical entity that actually stores the data.

There are two types of file, one is the rows data that stores it, and the other is log that stores logs. You can find the physical file on the disk from path.

The data stored in file is processed by SQL Server and stores the formatted data that SQL Server recognizes.

3. Benefits of using filegroups to manage files

In the actual development process, the user only needs to focus on the logical object filegroup, without concern for the physical storage of the data, even if the physical storage of file changes, does not affect the application. If the files within the same filegroup are distributed on different disks, when reading data, SQL Server can read data from different disks at the same time, greatly improving the IO performance, which is the basis of partition table implementation, the partition table can store different subsets of data on different disks.

Tsql View database's filegroup and file

Select *  from sys.database_files Select *  from Sys.filegroups

4, using File group case

4.1 When creating a table, using the ON clause to specify filegroup, the table's data is stored in the filegroup managed file.

CREATE TABLE [dbo].[Student](    [Stuid] [int]  not NULL,    [Stuname] [varchar]( -) not NULL,    [Professionid] [int] NULL,PRIMARY KEY CLUSTERED     (        [Stuid] ASC    ))  on [PRIMARY]

4.2 When creating an index, using the ON clause to specify filegroup, the index structure of the table is stored in the filegroup managed file.

CREATE nonclustered INDEX [idx_student_stuname]  on [dbo]. [student] [stuname] ASC  on[PRIMARY]

SQL Server filegroups and files