Tag: partitioning

SQL Server : Partitioning

Partitioning is an Enterprise Edition only feature, but all tables are actually partitioned. A regular table is a table with a single partition.

Create three file groups.

USE MyDatabase;
GO

ALTER DATABASE MyDatabase ADD FILEGROUP part_test_1_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_1_df',
FILENAME = N'e:\db\part_test_1_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_1_fg
GO


ALTER DATABASE MyDatabase ADD FILEGROUP part_test_2_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_2_df',
FILENAME = N'e:\db\part_test_2_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_2_fg
GO


ALTER DATABASE MyDatabase ADD FILEGROUP part_test_3_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_3_df',
FILENAME = N'e:\db\part_test_3_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_3_fg
GO

To partition a table we need a partition function. When defining a partition function we can use RANGE LEFT or RANGE RIGHT in the partition function.

  • RANGE LEFT: Value represents right hand boundary.
  • RANGE RIGHT: Value represents left hand boundary.

As an example, check out this partition function.

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

This means the following.

  • 0-1000 : Partition 1
  • 1001-2000 : Partition 2
  • 2001- : Partition 3

Create a partition scheme using the partition function.

CREATE PARTITION SCHEME myRangePS1
 AS PARTITION myRangePF1
 TO (part_test_1_fg, part_test_2_fg, part_test_3_fg) ;
 GO

Create a table using the partition scheme.

CREATE TABLE MyPartitionTable (
 id int PRIMARY KEY,
 col2 varchar(50)
 )
 ON myRangePS1 (col1) ;
 GO

It’s possible to switch a real table with a partition (partition exchange). In this example partition 3 in the partitioned table is swapped with the regular table (partition 1).

ALTER TABLE schema.part_table
 SWITCH PARTITION 3
 TO schema.normal_table PARTITION 1

The table definitions have to match (structure, keys etc.). Tables have to both be in the same file group.

For more information see: