what is the use of ndf files in sql server

Hi,

ndf-file are secondary database files. You can have one master (.mdf) and many secondary files.

http://technet.microsoft.com/en-us/library/ms189563.aspx

Such secondary files (ndf) are often placed on several storage system to spread the I/O workload. See Files and Filegroups Architecture

In addition to the answers above, I want to clerified a very important point! The file names are only conventions, and in SQL Server you can use any extension that you want including your name. In Unix like system (as Unix, Linux etc) there is no meaning to the file extensions, and this is just something that the windows operating system use (This can be change as well and configure differently).

As mentioned above, the the convention and the recommendation is to use file name extension for primary data files mdf , for the log files use ldf and for secondary data files ndf. If you want to mess with the other DBA's mind in your company you can use the opposite names (Just for fun! this is not recommended for real database, but a very nice exercise for interview exam)

For example: you can build a database with ldf file as the database file and mdf file as the log file. Then ask DBAs to attach the database to the server. Most DBAs will not succeed since they will try to atach the mdf file :-) again... DONT DO IT IN REAL DATABAD

In addition to the answers above, I want to clerified a very important point! The file names are only conventions, and in SQL Server you can use any extension that you want including your name. In Unix like system (as Unix, Linux etc) there is no meaning to the file extensions, and this is just something that the windows operating system use (This can be change as well and configure differently).

As mentioned above, the the convention and the recommendation is to use file name extension for primary data files mdf , for the log files use ldf and for secondary data files ndf. If you want to mess with the other DBA's mind in your company you can use the opposite names (Just for fun! this is not recommended for real database, but a very nice exercise for interview exam)

For example: you can build a database with ldf file as the database file and mdf file as the log file. Then ask DBAs to attach the database to the server. Most DBAs will not succeed since they will try to atach the mdf file :-) again... DONT DO IT IN REAL DATABAD

what is the use of ndf files in sql server

Advantages of NDF Secondary data file : http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6668cf34-02d5-4e44-95c6-cec3f062ce24/in-what-scenarios-would-secondary-data-file-be-useful?forum=sqlgetstarted