SQL Server files and filegroups

Introduction to files and filegroups

In SQL Server, there is no difference between how a database is stored on a hard disk and how ordinary files are stored in Windows, just a few files. SQL Server manages files by managing logical filegroups.

　　SQL Server manages data files through filegroups. The logical database we see is composed of one or more filegroups.

The structure diagram is as follows:

　　　　

　　　　the filegroup manages the files on the disk, The actual data for SQL Server is stored in the file.

Why manage files through filegroups

From the user's perspective, you need to specify only three kinds of data objects that store filegroups when creating objects: tables, indexes, and large objects (lobs).

Using filegroups allows you to isolate a user's dependency on a file, allowing users to set up tables and indexes only on filegroups, rather than on actual disk files. When files are moved or modified, SQL Server can manage files with confidence because the tables and indexes created by the user are built on filegroups and do not rely on specific files.

In addition, the use of filegroups to manage files, you can make files within the same filegroup distributed across different hard disks, can greatly provide IO performance.

SQL Server automatically assigns newly added space based on the initial size and increment value of each file setting, assuming that the same file A is set to twice times the size of file B, a new data occupies 3 pages, 2 pages are allocated proportionally to file a, and 1 pages are assigned to file B.

Classification of files

• Top documents: This file is a must, and there can only be one. This file contains additional information about the location of other files, with the extension. mdf.
• Secondary files: Can be built as many as possible, for different purposes of storage, the extension of. ndf.
• Log file: Holds the log with the extension. ldf.

The above-mentioned centralized file name extension can be arbitrarily modified, the default extension is recommended. After SQL Server, a file stream data file and a full-text index file are also added.

You can view the files in the SQL Server database by sys.database_files the system view.

SELECT name,type_desc,physical_name,state_desc,size,growth  from sys.database_files

The results are as follows:

　　

Note that the Type_desc column, the primary and secondary files, are rows, which means that rows are stored (row).

If a table is present in multiple files physically, the table's data page is organized as n (n for a specific number of files) for a B-tree. Instead of an object for a B-tree.

Creating and working with filegroups

Create a file or a combination of files through management Studio or by using T-SQL statements. For a database, you can add files and filegroups to an existing database, as well as files and filegroups at the time of creation.

To create a filegroup operation:

　　

To create a file operation:

　　

Note: Once a file group is set up, it cannot be changed.

　　The following is an example of an SQL instance that is specified to be placed into a filegroup when an index is created:

--　　 When you add an index, you specify which filegroup to store CREATE nonclustered INDEX  on On Addfilegroup --The red part is the protagonist, specifying the filegroup.  --　　 If you do not specify a filegroup, it is stored in the default filegroup CREATEnonclusteredINDEX  on Productdemo (Upcode)　　　　

advantages and disadvantages of using multiple files

In most cases, a small database does not need to create multiple files to hold the data. But as the data grows, the drawbacks of individual files arise.

• First, the use of multiple files to distribute to different disk partitions (multiple hard disks) can improve IO performance in several major ways.
• Second, multiple files are convenient for backup and recovery for databases with more data.
• However, multiple files require more disk space, because each file has its own set of B-tree organization and its own growth space. Of course there are fragments of their own.
• Overall, the advantages of multiple files are far greater than the drawbacks.