SQL Server 2008 R2 File Extensions & file groups

SQL Server uses the following three files types to store information;

.mdf - main database file
.ndf - (n) database file
.ldf - Log data file


When you create a database via the gui, by default you are given an .mdf file and a .ldf file. You are also given a PRIMARY file group, which contains the .mdf file.

A filegroup can contain one or more datafiles.

Sometimes, it can be beneficial to have more than one filegoup, for example for best practice you can separate the default system objects, which are created by the gui when you first create the database. You can create a secondary filegroup, with an .ndf file and then make this your primary file group once you have created the datanbase. This would provide you with greater isolation from I/O write activity.