1. Implications and relationships of documents and filegroups
Each database has one master data file. and several from files. Files are the physical embodiment of a database. Filegroups can include files that are distributed across multiple logical partitions to achieve load balancing. Filegroups allow you to group files to facilitate management and data allocation/placement. For example, you can create three files (DATA1.NDF, DATA2.NDF, and DATA3.NDF) on three hard drives, and assign the three files to the filegroup fgroup1. You can then explicitly create a table on the filegroup fgroup1. Queries on the data in the table are dispersed to three disks, thereby improving performance. Creating a single file on a RAID (redundant array of disks) stripe set can also achieve the same performance improvement. However, files and filegroups allow you to easily add new files to a new disk. In addition, if the database exceeds the maximum size of a single Microsoft Windows file, you can use a secondary data file to allow the database to grow.
2. Common problems in the application of file and file group in practice
Typically, we construct a database with only two files, MDF files, and LDF files. But there are two drawbacks:
(i) easy to cause the file too large
We know that the MDF file is a database file, this means that as the database increases MDF will increase, obviously in the current application of data expansion is too common, when your application becomes larger, MDF file will become larger, however, windows on the size of the file is required, This can easily cause the MDF file to reach the limits of the file size that Windows allows (and the database crashes).
(ii) No use of disk arrays
Large servers have a lot of disk arrays, you can make the disk array a simple illusion of n a rotating disk, the disk array is designed to be through the concatenation of multiple disks to achieve greater reading and writing efficiency. But if your database has only one MDF file (LDF file is not considered for the moment), Then you will always be able to use only one disk in this disk array. In that case, the efficiency of the expensive disk array is changed from parallel to concatenation. Imagine if we could split the MDF into multiple files, such as allocating a file to each disk on the disk array, and then distributing the data in MDF into each file , I read in tandem in the reading, so that the full use of disk array access efficiency.
These two problems are not normally met by us, but they are almost fatal when doing a large service development.
3. Query file groups and file statements
This is under 2005, running
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
Understanding Files and Filegroups
Each SQL Server database has at least two operating system files: One data file and one log file. Data files contain data and objects, such as tables, indexes, stored procedures, and views. The log file contains the information needed to recover all the transactions in the database. For ease of allocation and management, you can set up data files and put them in a filegroup.
à database file
The SQL Server database has three types of files, as follows
Main data file
The primary data file contains the startup information for the database 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 one primary data file. The recommended file name extension for the primary data file is. mdf.
Secondary data files
is optional, and user data is defined and stored by the user. Secondary files can be used to spread data across multiple disks by placing each file on a different disk drive. In addition, if the database exceeds the maximum size of a single Windows file, you can use secondary data files so that the database continues to grow. The recommended file name extension for secondary data files is. ndf.
Transaction log files
The transaction log file holds the log information used to recover the database. Each database must have at least one log file. The proposed file name extension for the transaction log is. ldf.
à file group
Each database has one primary filegroup. This filegroup contains the primary data files and all secondary files that are not placed in other filegroups. You can create user-defined filegroups to assemble data files for ease of management, data allocation, and placement.
For example, you can create three files data1.ndf, DATA2.NDF, and DATA3.NDF on three disk drives, and then assign them to a filegroup fgroup1. You can then explicitly create a table on the filegroup fgroup1. Queries on the data in the table are dispersed to three disks, which improves performance. The same performance gains can be achieved by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups enable you to easily add new files to a new disk.
The following lists all the data files that are stored in the filegroup.
Primary file Group
A filegroup that contains primarily the primary file. All system tables are assigned to the primary filegroup.
User-defined file group
Any filegroup that is explicitly created when the user first creates the database or modifies the database at a later time
Default file group
If you create an object in the database without specifying the filegroup to which the object belongs, the object is assigned to the default filegroup. Only one filegroup can be specified as the default filegroup at any time. The files in the default filegroup must be large enough to hold all new objects that are not assigned to other filegroups.
The PRIMARY filegroup is the default filegroup unless the change is made using the ALTER DATABASE statement. However, system objects and tables are still assigned to the PRIMARY filegroup rather than the new default filegroup.
5. Operation Example
Copy Code code as follows:
--(5.1) Create a database
--Switch to master database
Use master
Go
Create DATABASE Db_study
On primary--primary filegroup and primary data file
(Name= ' db_study_primary ',
Filename= ' D:\study\db_study_pri.mdf '),
Filegroup Db_study_filegroup1--user-defined file group 1
(Name= ' db_study_fg_data1 ',
Filename= ' D:\study\db_study_fg_data1_1.ndf '),--secondary data file 1
(Name= ' Db_study_fg_data2 ',
Filename= ' D:\study\db_study_fg_data2_2.ndf '),--secondary data file 2
Filegroup db_study_filegroup2
(Name= ' db_study_fg_data3 ',
Filename= ' D:\STUDY\DB_STUDY_FG_DATA3_1.NDF ')
Log on
(Name= ' Db_study_log ',
Filename= ' D:\study\db_study.ldf ')
Go
--5.2 query filegroups and files
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
/*
Name Physical_name size growth Filegroup Is_default
Db_study_primary D:\study\db_study_pri.mdf 280 128 Primary 1
DB_STUDY_FG_DATA1 D:\STUDY\DB_STUDY_FG_DATA1_1.NDF 128 128 Db_study_filegroup1 0
DB_STUDY_FG_DATA2 D:\STUDY\DB_STUDY_FG_DATA2_2.NDF 128 128 Db_study_filegroup1 0
DB_STUDY_FG_DATA3 D:\STUDY\DB_STUDY_FG_DATA3_1.NDF 128 128 db_study_filegroup2 0
*/
--5.3 Modify default data file group
ALTER DATABASE Db_study
Modify Filegroup Db_study_filegroup1 Default
--5.4
--Create a table in the default filegroup db_study_filegroup1,
--and specifies that the image data is saved in the user-defined filegroup db_study_filegroup1
CREATE TABLE My_test
(
ID int PRIMARY KEY,
[Name] varchar (10),
PIC image
) textimage_on db_study_filegroup2
--Create an index on the user-defined filegroup db_study_filegroup2
Create INDEX ix_my_test on My_test (ID) on db_study_filegroup2
Go
--5.5 data files will be deleted db_study_fg_data1 to other data files.
--and empty the data file db_study_fg_data1
DBCC Shrinkfile (Db_study_fg_data1,emptyfile)
Go
--Delete data file db_study_fg_data1
ALTER DATABASE Db_study
REMOVE FILE db_study_fg_data1
Go
