SQL Server uses .mdf for data files and .ldf for log files, but what are .ndf files?

What are the benefits of these files?

3

Best Answer


From Files and Filegroups Architecture

Secondary data files

Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

Also from file extension NDF - Microsoft SQL Server secondary data file

See Understanding Files and Filegroups

Secondary data files are optional, areuser-defined, and store user data.Secondary files can be used to spreaddata across multiple disks by puttingeach file on a different disk drive.Additionally, if a database exceedsthe maximum size for a single Windowsfile, you can use secondary data filesso the database can continue to grow.

The recommended file name extensionfor secondary data files is .ndf.

/

For example, three files, Data1.ndf,Data2.ndf, and Data3.ndf, can becreated on three disk drives,respectively, and assigned to thefilegroup fgroup1. A table can then becreated specifically on the filegroupfgroup1. Queries for data from thetable will be spread across the threedisks; this will improve performance.The same performance improvement canbe accomplished by using a single filecreated on a RAID (redundant array ofindependent disks) stripe set.However, files and filegroups let youeasily add new files to new disks.

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.

Source: MSDN: Understanding Files and Filegroups

The recommended file name extension for secondary data files is .ndf, but this is not enforced.

An NDF file is a user defined secondary database file of Microsoft SQL Server with an extension .ndf, which store user data. Moreover, when the size of the database file growing automatically from its specified size, you can use .ndf file for extra storage and the .ndf file could be stored on a separate disk drive. Every NDF file uses the same filename as its corresponding MDF file. We cannot open an .ndf file in SQL Server Without attaching its associated .mdf file.