tl;dr; Filegroups are a logical construct used to separate tables and indexes from each other, files are the physical construct used to store information (log & data) about the database.

When creating a database you’ll notice that each database is built of several components. There are two files, one for data and one for the log, the MDF and LDF files. Then there is the default filegroup PRIMARY. But that’s all just the default.

Filegroups

Filegroups are a logical construct for the data in a database. They are there so that, if you want, you can separate tables and indexes from each other. If you look at partitioning you will almost certainly learn something about filegroups, because part of the construction of partitions is the option to put each partition into its own filegroup. In fact, since even non-partitioned tables/indexes are essentially single partition tables/indexes every table or index can be placed into a specific filegroup. The filegroup can be specified when creating a table or index or it will use the default (usually PRIMARY). So, if filegroups are a logical construct, what’s is physical representation?

Files

Files are the actual location where the data is stored. There are two types of files, transaction log files and data files.

Transaction log files

The transaction log is part of what allows the database to be ACID compliant. For those that don’t know, that means transactions are Atomic, Consistent, Isolated and Durable. I’ll let you follow the link for the

Continue reading on SQLStudies.com.