MDF and NDF File
SQL Server database is made up of data files and log files. While the data files store data and objects like views, tables, rules, views etc; the log files stores transactions with every detail of operation performed on database (also helpful in various recovery scenarios). Meanwhile, the data files are categorized as Primary Database File and Secondary Database File. Let us have an introduction to them.
Primary Database (MDF)
Primary Data file (with suffix '.mdf') of SQL Server provides reference to start point of database and gives pointers to files that are connected with database as well as tables, columns, index etc. For every database, a primary database (.mdf) file gets created.
Some Facts about MDF File:
- FACT #1: Location of a Primary Data File is stored in Master Database (filename coloumn>> sysdatabases table)
- FACT #2: An MDF file is the part of default database File Group. Only one DB file is enough for storing tables and indexes.
- FACT #3: Header details of primary data file is stored in sysfiles table. It also stores logical and physical filename of database.
About Primary System Database of SQL Server:
- Master Database: The main job of master.mdf file is to keep a track of location for various database files, system configuration, information about existing DBs, Servers that are connected together, or the endpoints.
- Model Database: The model.mdf gives a template for creating user-database. When a CREATE DATABASE statement is passed, model database of is copied from previous DB to first part of new DB (the remaining database has void pages).
- MSDB Database: The main task of msdb.mdf file is to schedule jobs for backup and restore history. For example: Name of the party that created online backup, at what time it was restored etc. Moreover, this primary data file is used by other applications like SQL Server Management Studio for task like restoring backup and replaying transaction logs.
- TEMP Database: Temporary operations that performed while an instance is running, for example deleting, modification are performed by temp.mdf file. It is also used for objects like table variables, indexing etc (indicates that no object get saved permanently).
- Resource Database: This database is hidden in SQL Server Management Studio and presents read-only version of system objects that exist in sys.objects of database.
Secondary Database (NDF)
If storage space of primary data file is not enough, a new file can be created that adds up data in continuation to it. These are secondary database (.ndf) files that are added to default or to different file group defined by the database.
Related Terms: For logical organization of database, administrators use File Groups. There are two types of file groups:
Primary: This is the default location of MDF files. All system tables are created in this file group.
User Defined: This file group is created using CREATE DATABASE or ALTER DATABASE command where details about the DB are entered like file name, file directory etc.