NDF did you say, are you sure you don’t mean MDF?
This is common reaction I observe by many students who have attended my course. And as many of the students are looking after third party applications, the data is typically stored within the default MDF files and no thought is given to the fact that within SQL Server, you can also create secondary data files, known as NDF files
I can sometimes forget when running User groups or writing a blog that there are still many people out there embarking on a new and exciting career surrounding Microsoft SQL Server. Understanding .MDF and NDF files is an important part of creating a SQL Server database
The MDF file is a primary data file that automatically created when you create a new database in SQL server. There is only one and it is mandatory and by default will contain the system metadata specific to the database. Left with this default setup, any user defined tables will be stored in the same MDF file which can cause contention as SQL Server will read from the same MDF file for both the system metadata and the user data.
My mentor also said, get some NDF files created when creating a new database. These are secondary data files and you are not limited to creating just one. This way you can map your user defined tables on an NDF file and keep the system metadata on the primary MDF file. This can spread the load and improve performance, especially if the MDF file or NDF file are located on separate physical disks on separate controllers.
How you place data into the separate files is controlled by tables and filegroups, and I will save that for another blog.
For more information regarding MDF files and NDF files, the following Microsoft SQL Server 2008 Courses can help
Microsoft E-Learning Course 6369
Microsoft Course 6231
If you need any more information about these course or other SQL Server courses, please feel free to ping me