Data file in MS SQL

MSSQL-Feature-Image

Data file is an file which store all object and data of a database.
There are two type of data file: Primary data file and Secondary data file.

Primary data file is a one and only one data file for storing all objects and data of a database. It is a default data file for database. It’s file extension is “XXX.MDF

Secondary data file is created when required. it’s file extension is “XXX.NDF

How to check current data file setting:
1. Right-click suitable database object > select Properties
2. Select “File” page > View “Database files:
3. “File Name” & “Path” show data file’s file name and its path.

How to create “Secondary data file” via GUI:
1. Right-click suitable database object > select Properties
2. Select “File” page > click “Add
3. Fill in “Logical Name” and “File Name
4. Change “Path” if need

How to remove “Secondary data file” via GUI:
1. Right-click suitable database object > select Properties
2. Select “File” page > click “Remove

How to create “Secondary data file” via T-SQL:

How to remove “Secondary data file” via T-SQL:

How to move user database’s file via T-SQL:

 

Author: Joe Chan