Creating Partitions in SQ2K5

Chapter 6 Creating Partitions

Scripting already existing Partition Schemas and Partition Functions

Using management studio in the object explorer, look under /Storage - There you will find Partion Schemas and Partition Functions. From there you can script the objects.

1. To partition a table or index, you perform the following tasks:

1. Create a partition function
2. Create a partition Scheme mapped to a partition function
3. Create the table or index on the partition scheme

2. Partitioning is one SQL Server 2005 feature that does not have graphical user interface (GUI) in SSMS. You must code to perform all operations related to partitions

3. Creating a partition function (Page 210)

General Syntax

CREATE PARTITION FUNCTION partition_function_name (input_parameter_type)

AS RANGE [LEFT RIGHT]

FOR VALUES ([boundary_value…n])

Eg.

CREATE PARTITION FUNCTION partfunc (int)

AS LEFT

FOR VALUES (1000, 2000, 3000)

This Eg. Creates a partition function named partfunc that is applied to values of data type integer.

The range LEFT clause in the example specifies that each boundary point defined for the function resides in the left hand partition.

4. Creating a partition Scheme mapped to a partition function (page 215)

General Syntax

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

TO (file_group_name...n)

Eg.

CREATE PARTITION SCHEME partscheme

AS PARTITION partfunc

TO ([FG1], [FG2])

5. Create the table or index on the partition scheme

Eg.

CREATE TABLE dbo.Customer.Address

(CustomerAddressID int)

on partscheme (CustomerAddressID)

6. Querying Partitions

$PARTITION function returns a partition number based on the column value for a particular partition function. It is used for

• Determine the partition number to which a particular value would respond

Select $partition.partfunc (2784) as [PartitioNum]

• Restrict a query to a specific partition

Select * from dbo.CustomerAddress where $partition.partfunc (CustomerAddressID) =3

7. Managing partitions: Split and Merge (page 226)

ALTER PARTITION FUNCTION partfunc () SPLIT RANGE (6000);

ALTER PARTITION FUNCTION partfunc () MERGE RANGE (1000);

8. SWITCH

You can use SWITCH operator of ALTER TABLE command to

• add rows to a table
• delete rows from a table

advantage is that it does not create locks as delete and insert does.

ALTER TABLE dbo.t2 SWITCH TO dbo.t1

Where dbo.t2 and dbo.t1 have same structure and indexing