file types in sql server database

There are three main file types

• MDF
• NDF
• LDF

MDF - Primary data file

Starting point of the database.This points to other files inside database.Every database has one primary data file.

All the data in the database objects are stored inside primary data file.(tables, stored procedures, triggers, views,....etc)

NDF - secondary data files

can have only one primary file and maintain any number of secondary data files

Optional - not necessary to have.

LDF - Log files

hold the all the log information

you can use this to recover database

size of log file depends on the log level defined by the database administrator

Should have at least one log file. ( can have multiples)

So basically you should have a primary data file and log file for each database.

mentioned extensions for the each category are optional and you can have any extension name instead of (.mdf, .ndf, .ldf) , but it is recommend to follow the standard. unless you have a necessity.