When we create a database, the CREATE command create a data file with extension .mdf is a primary file. It is a SQL Data files where all the data in the database objects are stored inLike tables, stored procedures, views, triggers etc.
All are stored in the .mdf file of sql server, This is the reason whey we use .MDF file attached to the database. Once we attach the .mdf we were able to see all the data exist in that respective database.
.ldf is the log file in the SQL server to see the dataTransaction log file with extension .ldf.
The size of the log file (ldf) is determined my the logging level you have set up on the database.
Simple, full, and build logged are the options Simple being the least, and full being the most, the logged (if in full) will allow you to re-apply transactions to the database.
Incase of a failure If your looking for some performance improvement, there are a lot of things that can be done.
.ndf are secondary databse filesSame as mdf, but you can only have 1 Main database file. This file also could be stored on a separate physical drive.
Advantage is that, tables that are relatively static are in another file Tables that are written frequently are stored in one file Having to smaller file to write to for transactions will help with throughput.