What are NDF Files in SQL Server?
What are NDF Files in SQL Server? Sql Server interview questions Filegroups: All database files are grouped into the filegroups. A filegr...
All database files are grouped into the filegroups. A filegroup is a logical unit that simplifies database
administration. It permits the logical separation of database objects and physical database files. When you create database objects-tables, for example, you specify into what filegroup they should be placed without worrying about the underlying data files’ configuration.
Every database has one primary data file, which by convention has an .mdf extension. In addition, every database can also have secondary database files. Those files, by convention, have .ndf extensions.
Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow.
The ability to put multiple data files inside a filegroup lets us spread the load across different storage drives,which could help to improve the I/O performance of the system.
The recommended file name extension for secondary data files is .ndf
Example: Here we will create diff filegoups and will create a table and move that table in secondary filegroup
(NAME = Test_data1, FILENAME = 'C:\Test_data1.mdf'),