SQL Server FileGroups?

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. The use of Filegroup is limited to Data Files only. Database objects and files can be grouped together in filegroups for allocation and administration purposes. There are two types of SQL Server filegroups:

Primary Filegroup

The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.

User-defined [Secondary] Filegroup

User-defined filegroups are filegroups that are specified by using the filegroup keyword in a create or alter Database statement. You cannot assign single file to multiple filegroups. Objects like Table, Indexes can be associated to a Specified Filegroup. By default, whenever you create any object it gets associated with Primary Filegroup until and unless a Filegroup is specified.

There are N number of reasons to use File Groups but the basic reason is to get Data Placement Control in a Database. [In case you want to know more visit:- When to create Multiple Files & Multiple Filegroups for a Database?]

Explanation: By default you cannot force SQL Server to place a certain object on specific Disk or file, once you create a filegroup and add a file to it you can force SQL Server to place the object on that filegroup at the time of its creation. If you are not specifying any filegroup sql server creates the object on its default filegroup i.e., primary filegroup.

Let’s test this quickly by creating a table in secondary filegroup:

--create database
create database EbayZone
go
 
use EbayZone
go
	 
--add filegroup
ALTER DATABASE EbayZone
ADD FILEGROUP PurchaseOrderData;
go
	 
--add file to new created filegroup
ALTER database EbayZone
ADD FILE
(
    NAME= 'purchase_order',
    FILENAME = 'D:\purchaseOrder_file.ndf'
)
TO FILEGROUP PurchaseOrderData
 
--create table on new created filegroup
CREATE TABLE [dbo].[PODetail]
(
    [PurchaseOrderID] [int] NOT NULL Primary Key,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,    [OrderQty] [smallint] NOT NULL
) 
ON [PurchaseOrderData];

To verify the object location

--cross verify (Locate the entry named Data_Located_on_filegroup in ResultSet)
sp_help [PODetail]

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook