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 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.
NDF Files:
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
Filegroups:
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.
NDF Files:
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
/* Create a test database with 3
filegroups */
USE master;
GO
CREATE DATABASE Test ON
PRIMARY
(NAME = Test_data1, FILENAME = 'C:\Test_data1.mdf'),
PRIMARY
(NAME = Test_data1, FILENAME = 'C:\Test_data1.mdf'),
FILEGROUP [Test_Secondary]
(NAME = Test_data2, FILENAME = 'C:\Test_data2.ndf'),
FILEGROUP [Test_Archive]
(NAME = Test_data3, FILENAME = 'C:\Test_data3.ndf')
LOG ON(NAME = Test_log, FILENAME = 'C:\Test_log.ldf')
GO
Now Create a table in this database and move it into Test_Secondary filegroup
/* Create a sample table and put it
inside Test_secondary filegroup */
USE Test
GO
CREATE TABLE dbo.employee
(emp_id int,emp_fname varchar(10), emp_lname varchar(10)) on [Test_Secondary]
GO
Comments