In this article, we are talking about Files and File Groups in SQL Server. when you create a simple database in SQL SERVER using CREATE DATABASE command. There are 3 types of Files in SQL SERVER DATABASE, they are
- Primary File
- Secondary File
- Transaction Log File
When we create a DATABASE. The Primary file Stores the startup information of the DATABASE. And it looks into the remaining files in the DB. It stores USER information and objects or secondary file also stores this data. When we create a DATABASE one primary data file created. Each DB can have one primary Data File and the primary file extension is .mdf.
These secondary data files are an optional one. And these are user-defined files. This secondary file used to distribute the information to different disks (many drives). If a DB cross the most size for one file. We can use this secondary file so that DB can continue to work. And .ndf is the recommended extension for this file.
Transaction Log File
This log file holds the log data used to restore the DB. Each DB must have one Log File. The extension for this Log file is .ldf.
Each DB can have one primary filegroup. And it contains the primary data file, secondary files those not placed in any other file groups. USER defined file groups can be made to group the information records together. For administrative, information allotment, and arrangement purposes.
Example, 3 files, info1.ndf, info2.ndf, info3.ndf can be made on three drives and assign to filegroup1. One table created particularly on file group filegroup1. Questions for information from the table will spread over the three disks. This will enhance execution. A similar execution change can be proficient by utilizing a single file made on a RAID stripe set.
All these data files located in file groups listed below
Primary File Group: The file group that contains the essential record. All sys tables allotted to this file group.
USER-Defined File Group: Any file group that is particularly made by the client (user). When the client makes or later adjusts the database.
File and Filegroup Example
CREATE DATABASE MyDB
FILENAME=’c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf’,
(NAME = ‘MyDB_FG1_Dat1′,
FILENAME =’c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf’,
SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB),
( NAME = ‘MyDB_FG1_Dat2′, FILENAME =’c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf’,
SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB)
( NAME=’MyDB_log’, FILENAME =’c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf’,
SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);
ALTER DATABASE MyDB
Change FILEGROUP MyDB_FG1 DEFAULT;
— user-defined filegroup.
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
Rules for Designing Files and Filegroups
- A File or filegroup can’t be utilized by more than one DB.
- A file can be an individual from a single file group.
- In any file groups .ldf files not become a part