Filegroup

Filegroup is an organizational metadata object containing one or more data files. Filegroup does not have its own representation in the filesystem--it's just a group of files. When any database is created, a filegroup called primary is always created. This primary filegroup always contains the primary data file.

Filegroups can be pided into the following:

• Row storage filegroups: These filegroup can contain data files (mdf or ndf).
• Filestream filegroups: This kind of filegroups can contain not files but folders to store binary data.
• In-memory filegroup: Only one instance of this kind of filegroup can be created in a database. Internally, it is a special case of filestream filegroup and it's used by SQL Server to persist data from in-memory tables.

Every filegroup has three simple properties:

• Name: This is a descriptive name of the filegroup. The name must fulfill the naming convention criteria.
• Default: In a set of filegroups of the same type, one of these filegroups has this option set to on. This means that when a new table or index is created without explicitly specified to which filegroup it has to store data in, the default filegroup is used. By default, the primary filegroup is the default one.
• Read-only: Every filegroup, except the primary filegroup, could be set to read-only. Let's say that a filegroup is created for last year's history. When data is moved from the current period to tables created in this historical filegroup, the filegroup could be set as read-only, and later the filegroup cannot be backed up again and again.

It is a very good approach to pide the database into smaller parts--filegroups with more files. It helps in distributing data across more physical storage and also makes the database more manageable; backups can be done part by part in shorter times, which better fit into a service window.