Creating Partition Table In Sql Server 2012

The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable.

We can create a partitioned table or index in SQL Server 2012 in following four steps.

• Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme. 
• Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column. 
•  Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups. 
•  Create or modify a table or index and specify the partition scheme as the storage location. 

Permissions Required for Partitioning

Creating a partitioned table requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created. 

• ALTER ANY DATASPACE permission. 
• This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles. 

•  CONTROL or ALTER permission 
• On the database in which the partition function and partition scheme are being created. 

• CONTROL SERVER or ALTER ANY DATABASE permission 
• On the server of the database in which the partition function and partition scheme are being created.  

 
Example

Through GUI

Step 1: Create File Groups

 

Step 2: Create Data Files for your partitions.

Step 3: Partition the table.

Step 4: Specify boundries for partitions.

If you selected Left boundary on the Map Partitions page, this date will be the last value for each filegroup/partition. If you selected Right boundary on the Map Partitions page, this date will be the first value in the next-to-last filegroup.

Step 5: Finish the wizard.

Through Query


USE Sample; 
GO 

-- Adds four new filegroups to the Sample database 

ALTER DATABASE Sample 
ADD FILEGROUP test1fg; 
GO 

ALTER DATABASE Sample 

ADD FILEGROUP test2fg; 
GO 

ALTER DATABASE Sample 
ADD FILEGROUP test3fg; 
GO 

ALTER DATABASE Sample 
ADD FILEGROUP test4fg; 

-- Adds one file for each filegroup. 

ALTER DATABASE Sample 
ADD FILE ( NAME = test1dat1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) 
TO FILEGROUP test1fg; 

ALTER DATABASE Sample 
ADD FILE ( NAME = test2dat2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) 
TO FILEGROUP test2fg; 
GO 

ALTER DATABASE Sample 
ADD FILE ( NAME = test3dat3, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) 
TO FILEGROUP test3fg; 
 GO 

ALTER DATABASE Sample 
ADD FILE ( NAME = test4dat4, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) 
TO FILEGROUP test4fg; 
GO 

-- Creates a partition function called myRangePF1 that will partition a table into four partitions 

CREATE PARTITION FUNCTION myRangePF1 (int) 
AS RANGE LEFT FOR VALUES (1, 100, 1000) ; 
GO 

-- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above 

CREATE PARTITION SCHEME myRangePS1 
AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; 
GO 

 -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1 

CREATE TABLE PartitionTable 
(
 col1 int PRIMARY KEY,
 col2 char(10)
) ON myRangePS1 (col1) ; 
GO 

Checking if a table is patitioned/No. Of Partitions

SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
    ON  p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
    AND t.name = 'PartitionTable';

Determining Boundry value for partitions

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue , p.rows
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'PartitionTable' AND i.type <= 1
ORDER BY p.partition_number;

Determining Partition Column

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.columns AS c
    ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
    ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'PartitionTable'
AND i.type <= 1
AND c.column_id = 1;