Archive

Posts Tagged ‘sys.partitions’

SQL Server – How to partition an existing table…

09.08.2011 5 comments

It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement.

 

We still need to create a partition function and a partition scheme, I have posted earlier about how to create the same here. Step 1 – Creating a partition function and Step 2 – Creating a partition scheme are same, only Step 3 changes as we are partitioning an existing table.

 

For example, I have a table dbo.Table_Orders which is not partitioned:

SELECT      OrderID, OrderDate, Quantity, Amount

FROM        dbo.Table_Orders

 

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

OrderID     OrderDate  Quantity    Amount

———– ———- ———– ———————

1           2011-07-22 34          3200.00

2           2010-06-22 98          9800.00

3           2009-05-22 65          6500.00

4           2011-07-24 73          7300.00

 

(4 row(s) affected)

 

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039631872    5575058     1                4

 

(1 row(s) affected)

 

Now, if we want to partition this table, we need to create a clustered index ON a partition scheme. I have a partition scheme in my database ordersPartScheme. So the clustered index should be created using:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) ON ordersPartScheme(OrderDate)

 

— Check for new partitions

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039762944    5575058     1                1

72057594039828480    5575058     2                3

 

(2 row(s) affected)

From the result set, we can see that new partition is added to the table, and required rows are also moved to new partition.

 

However, if you already have a clustered index created on a table, then you can use DROP_EXISTING clause to re-create the clustered index:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) WITH (DROP_EXISTING = ON)

ON ordersPartScheme(OrderDate)

Using DROP_EXISTING = ON specifies that the index is dropped and rebuilt.

For more information, check Horizontally partitioning a SQL Server Database Table.

Hope This Helps!

Vishal

SQL Server – Horizontally partitioning a SQL Server Database Table

22.07.2011 5 comments

Partitioning was introduced in SQL Server 2005, it allows you to different database files, which can be located on different disks to improve performance. In earlier versions this was achieved by creating separate tables on different file groups of database, and then creating a view by UNIONing these tables to make it look like a single table.

First, let us create a Test database for partitioning with more than one file groups. I have created [PartitionDB] with two file groups – [PRIMARY] and [FileGroup2], both of these groups contain a data file:

CREATE DATABASE [PartitionDB] ON

PRIMARY

      (     NAME = N'PartitionDB',

            FILENAME = N'C:PartitionDBPartitionDB.mdf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      ),

FILEGROUP [FileGroup2]

      (     NAME = N'PartitionDB_2',

            FILENAME = N'C:PartitionDBPartitionDB_2.ndf' ,

            SIZE = 3072KB , FILEGROWTH = 1024KB

      )

LOG ON

      (     NAME = N'PartitionDB_log',

            FILENAME = N'C:PartitionDBPartitionDB_log.ldf' ,

            SIZE = 9216KB , FILEGROWTH = 10%

      )

image

Note: I have created both files on the same disk here, it is recommended to create files on separate disks for optimal performance.

Now we have a database to work with, let’s start with table partitioning, Partitioning a Table involves three steps:

 

Step 1: Creating a Partition Function – This is the first step in creating a partitioned table, a partition function tells SQL Server “how” to split the data. A partition function is not related to any particular table, it is a “technique” that SQL Server will use to split the data rows.

A partition function defines the range of values for a partition. For example, customer order data can be partitioned according to order date. I.e. data for current year will be in [PRIMARY] file group, while older data will be in [FileGroup2] file group. To achieve this we can create partition function as:

— Step 1. Creating a Partition Function

 

CREATE PARTITION FUNCTION ordersPartFunc (DATE)

AS RANGE LEFT

FOR VALUES ('2010-01-01')

The VALUES(<list of values>) define a list of boundary values for each partition, By specifying VALUES (2010) we have created two partitions.

1. For Values < 2010 and,
2. For Values > 2010

RANGE defines to which partition the boundary values will go. It can either be specified as RANGE LEFT or RANGE RIGHT, depending on this the placement of boundary values is decided. Here, we have used RANGE LEFT that means ‘2010’ will go to LEFT partition, so the partitions will contain below data:

1. Values <= 2010 and
2. Values > 2010

 

Step 2: Creating a Partition Scheme – Once the partition function is created, SQL Server knows “how” to split data, but it does not know where to put this partitioned data, this is defined by partition scheme. Partition scheme need to be linked to a partition function, to specify where each partition will be stored. We can create a partition scheme linked to ordersPartFunc as:

— Step 2. Creating a Partition Scheme

 

CREATE PARTITION SCHEME ordersPartScheme

AS PARTITION ordersPartFunc

TO ([FileGroup2], [PRIMARY])

 

Note: A Partition scheme can only be created for file groups, and not individual data files.

Each file group defined in TO(<list of file groups>) corresponds to VALUES(<list of values>) in partition function. This scheme defines that:

1. [FileGroup2] will contain data for Values <= 2010 and,
2. [PRIMARY] will contain data for Values > 2010

We have now created the partition function and partition scheme, but we haven’t linked these to any tables yet. This can be done while creating the Table. A partition scheme can be applied to more than one table.

 

Step 3: Creating a Partitioned Table – Once he partition scheme is defined, a table can be created using the partition scheme. It is done by specifying the partition scheme in the “ON” clause while creating the table. Only partition scheme need to be specified, you don not need to specify a partition function as this is already defined in partition scheme:

— © 2011 – Vishal (http://SqlAndMe.com)

 

— Step 3. Creating a Partitioned Table

 

CREATE TABLE Table_Orders

(

      OrderID     INT,

      Name        VARCHAR(20),

      OrderDate   DATE

)

ON ordersPartScheme (OrderDate)

That’s all folks, data inserted to ‘Table_Orders’ will be partitioned according to ordersPartFunc, and will be stored according to ordersPartScheme.

Let’s insert some data to check if the partitions are created successfully.

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (1, 'Vishal', '2011-07-22')

— Will go to Partition 2 -> [PRIMARY] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (2, 'Vishal', '2010-06-22')

— Will go to Partition 1 -> [FileGroup2] File Group

 

INSERT INTO Table_Orders (OrderID, Name, OrderDate)

VALUES (3, 'Vishal', '2009-05-22')

— Will go to Partition1 -> [FileGroup2] File Group

 

SELECT      OrderID, Name, OrderDate

FROM        Table_Orders

Result Set:

OrderID     Name                 OrderDate

———– ——————– ———-

3           Vishal               2009-05-22

1           Vishal               2011-07-22

2           Vishal               2010-06-22

 

(3 row(s) affected)

You can use sys.partitions catalog view to check number of partitions and rows for a table as below:

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594038910976    5575058     1                1

72057594038976512    5575058     2                2

 

(2 row(s) affected)

sys.partitions can tell how many rows are present in each partition, you can use $PARTITION function to identify a partition for a row. $PARTITION can be used $PARTITION.partition_function_name(expression):

SELECT      OrderID, Name, OrderDate,

            $PARTITION.ordersPartFunc(OrderDate)

            AS 'Partition'

FROM  Table_Orders

Result Set:

OrderID     Name                 OrderDate  Partition

———– ——————– ———- ———–

3           Vishal               2009-05-22 1

1           Vishal               2011-07-22 2

2           Vishal               2010-06-22 2

 

(3 row(s) affected)

Hope This Helps!

Vishal