SQL Server understands files and filegroups

In SQL Server, where a database is stored on a hard disk like a regular file store, SQL Server manages the files that store the data by managing logical filegroups.

The filegroup manages the files on the disk, and the files contain the actual data of SQL Server

Why manage files through filegroups?
Use filegroups to isolate users and files so that users can create tables and indexes on filegroups instead of actual disk files.
When files are moved or modified, the user-created tables and indexes are built on filegroups and are not dependent on files, thus enhancing manageability

One more thing: using filegroups to manage files can make different files within the same filegroup on different disks, greatly improving IO performance

SQL Server automatically allocates newly added space based on the initial size and growth of each file set, assuming that file a in the same filegroup is set to twice times the size of file B, a new data occupies three pages, 2 pages are allocated proportionally to file A, 1 pages are assigned to file B

File classification:
1 primary file with. mdf extension (only one)
2 times to file, you can build multiple, for different purposes to store the extension. ndf
3rd log files, storing logs with an. ldf extension
2008 Add file stream data file and full-text index file after

Create a new database: You can add files and filegroups at the time of creation, as well as adding files and filegroups to an existing database

In the Properties Options file group for the database,
Primary is set to checked, the file group is added by default when the table or index is created
File groups are created so that you can add files to the group.
Once the filegroup to which the database file belongs is set, it cannot be changed

Advantages and disadvantages of using multiple files:
Typically: small databases do not need to create multiple files to distribute data, but as data grows,
Using multiple files to distribute data to multiple drives can greatly improve IO performance
Second: Multiple files are easy to backup and restore for a slightly more data base

Multiple file disadvantage: Need to occupy more disk space, because each file has its own set of B-tree and growth space, of course, there are some fragments, but more than the disadvantage of disk space is much less than the benefits of multiple files

SQL Server understands files and filegroups