Partition Table in SQL Server 2008

In this blog, I am going to explain what is partition table in SQL Server and how to implement with complete steps.

 

What is partition table:

 

Database table partitioning is the process where very large tables are divided into multiple parts. The data in partitioned tables is horizontally divided into units that can be spread across more than one filegroup in a database.

 

Partitioning can make large tables more manageable and scalable. The main of goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations

 

What is filegroup:

 

Database objects and files can be grouped together in filegroups for allocation and administration purposes. There are two types of filegroups:

 

Primary file group: 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 file group: User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

 

Steps to create partition table.

 

Step 1 Create partition function

create partition function cust_part_func(int)
as range right
for values(100,200,300,400,500)

Note: Numbers are used to represent range of value and number of partition.

 

Step 2 Create partition scheme

create partition scheme cust_part_scheme
as partition cust_part_func
to(fgp1,fgp2,fgp3,fgp4,fgp5,fgp6)

Note: fgp1....6 is name of file group

 

Step 3 Create file group for database

1.  Right click on database
2. Click Property
3. Click File
4. Add file
   fg1
   fg2
   fg3
   fg4
   fg5
   fg6
5. Add associated file group to file

Step 4 Create partition table

create table cust_part_table
(
EMPID int identity(1,1)not null,
expDate datetime null
)
on cust_part_scheme(empid).

Step 5 Insert value to verify partition table

declare @i int
set @i=0
while @i<10000
begin
insert into cust_part_table values(GETDATE());
set @i=@i+1
end

Step 6 Select statement to check partition result

select $partition.cust_part_func(empid) as 'Partition Number',* from cust_part_table

Note: Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.

 

Thanking you. If you have any suggestion or query, kindly revert me back with comments.