SQL Basic - Database File Types & File Group

mdf: main / primary database file also know as primary filegroup, consist all medata information (Database Catalogs) like sysobject, sysindex, syscolumns, sysroutines, sysuser. as well as data, if secondary file (File Group) is defined.

ldf: Transaction log file

ndf: Secondary file, also know as file-group file
A single database has multiple transaction log file (ldf) and secondary file (ndf) but a single primary data file (mdf). There are many advantages of storing data in different Files. We can put your read only tables or SCD tables in one filegroup and read write tables in another filegroup and take backup of only filegroup that has read write data. Creating additional files on different physical hard disks also improves I/O performance.

Using separate filegroup for tables that frequently joined
Using separate filegroup for indexes
Using separate disk for Log file
Using multiple files and filegroups can help improve the performance of join operations. By separating tables that are frequently joined into separate filegroups and then putting files within the filegroups on separate disks the separated I/O paths can result in improved performance. It is recommended for performance and recovery purposes that, if multiple filegroups are to be used, the primary filegroup should be used only for system objects and secondary filegroups should be used only for user objects. This approach improves the ability to recover from corruption. Spreading a database into multiple files, even on the same drive, makes it easy to move the database files onto separate drives, making future disk upgrades easier. SQL Server can take advantage of multiple filegroups by accessing tables and corresponding nonclustered
indexes using separate I/O paths. A nonclustered index can be created on a specific filegroup.

ALTER DATABASE AdventureWorks2008R2 ADD FILEGROUP Indexes ;
ALTER DATABASE AdventureWorks2008R2 ADD FILE (NAME = AdventureWorks2008_Data2j,
FILENAME = 'C:\DATA\AdventureWorks2008_2.ndf',
SIZE = 1mb,
FILEGROWTH = 10%) TO FILEGROUP Indexes;

CREATE INDEX IndexBirthDate
ON HumanResources.Employee (BirthDate)
ON Indexes;