Physical structure of database in MS SQL Server

The physical structure of the database is divided into the MDF, NDF and LDF. We can say Primary Data files, Secondary data files and Log files respectively.

MDF (Primary Data files)

It store data of tables, stored procedures, views, triggers etc. The file name extension for primary data files is .mdf. 

NDF (Secondary data files)

An NDF file is a user defined secondary database file of Microsoft SQL Server with an extension .ndf, which store tables, stored procedures, views, triggers data. Moreover, when the size of the database file growing automatically from its specified size or we apply partition table, we can use .ndf file for extra storage and the .ndf file could be stored on a separate disk drive.

LDF (Log files)

Log files in SQL Server databases hold all the log information of transactions. Those information can be later used to recover the database in case of any mismanagement. The file name extension for primary data files is .ldf. 

When we create a database, it creates a data file with .mdf extension and log file with .ldf extension. For example if we create a database named “My_Database” in SQL Server then by default  it will create 2 files named “My_Database.mdf” and “My_Database_Log.ldf”.