FILE GROUP

The database files are stored on your hard drive with an extension of .MDF(Master Data File) and is named as Primary Data File. Every database must have at least one primary data file. This file may hold not only the data for the database, but also the location of all the other files that make up the database, as well as start-up information for the database catalog.

It is also possible to have secondary data files. These would have the suffix .NDF. Secondary data files allow you to spread your tables and indexes over two or more disks. The upside is that by spreading the data over several disks, you will get better performance.

By dividing a single MDF file into multiple filegroups we get better performance in context of Insertion,Updation and Selection.

The reason behind to split .MDF file and place them to different hard disks is to Increase Data Access and Fetching speed. If all the data is on single hard disk then at the time of accessing or fetching the head of the hard disk moves to different sectors on the same hard disk, this consumes time and affect performance. So if the .mdf file is on multiple hard disk then heads of these hard disks moves separately and that increase the data accessing and fetching.

For Better Performance Create different type of objects of database on different file groups.

How To Divide .Mdf File into Multiple Files?

1.Create Database  and Name it as TryFS

2.Select the File Group Option on the left side of the window.

3.Click on the Add Button and Add different FileGroups.

4.Come Back to General Tab and Click the Add Button to add file group, name it and Select File group From filegroup dropdownlist.

5.Default path to Different Hard Disks. Here I have use only drive D and E on the same system(on a single hardisk).But we can add path of different Hard Disks or Systems Attached to the Network.

6.Shows the part of .mdf file is saved in D Drive.

7. Shows the Remaining two Files are saved in E Drive with .ndf extention.

 

CREATE DATABASE OBJECTS ON DIFFERENT FILEGROUPS.

1.Department Table Created on Primary File Group.

USE [TryFG]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Department](

      [DeptId] [int] NOT NULL,

      [DeptName] [nvarchar](50) NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED

(

      [DeptId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

2. Employee Table Created on Secondary File Group.

USE [TryFG]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Employee](

      [EmployeeId] [int] NOT NULL,

      [EmployeeName] [nvarchar](50) NOT NULL,

      [EmployeeAddress] [nvarchar](50) NOT NULL,

      [DeptId] [int] NULL,

 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

      [EmployeeId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [Secondary]

) ON [Secondary]

GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DeptId])

REFERENCES [dbo].[Department] ([DeptId])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]

GO


In this way we can Create different Database objects on different FileGroups.