Partition Table

The Table partitioning can make very large tables and index easier to manage and improve the performance of the query. This article is related to table partitioning concept and how you crate the partition table. Hope all of my readers will like it.

History

Table partitioning was introduced in SQL Server 2005 Enterprise Edition and enhanced in SQL Server 2008 Enterprise.

Intro

Growing of database is a fact and when it grows more than the hundreds of GB, it can be more difficult to load new data, move old data and maintain indexes. Just the sheer size of the table causes such operations to make much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical.

The Microsoft SQL Server 2008 provides table partitioning to make such operations more manageable.

Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table. In addition, the SQL Server optimizer can direct properly filtered queries to appropriate partitions rather than the entire table.

Way of Partition

There are two primary way to partition data into multiple tables.

Horizontal Partitioning

Where the selected subset of rows are placed in different tables. A view is created over all the tables, and queries directly to the view, the result is partition view.

Vertical partitioning

Where the columns of a very wide table are spread across multiple tables containing distinct subsets of the columns with the same number of rows. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view. SQL Server does not provide built-in support for vertical partitioning, but the new sparse columns feature of SQL Server 2008 can be a better solution for tables that require large numbers of columns.

SQL Server's table partitioning differs from the above two approaches by partitioning a single table: Multiple physical tables are no longer involved. When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned.

The Technology at a glance

A partitioned table is a unique kind of table in SQL Server and It depends on two pre-existing objects.

                                     1.   The Partition function

                                     2.   Partition scheme

The bellow model describes it in graphically.

Partitioned Table àPartition Schema àPartition Function

A partition table has columns and that is identified as partition columns and the column is referenced when the table is created. The partitioned table must be created on a partition scheme, which defines the filegroup storage locations for the partitioned table. The partition scheme in turn depends on a previously created partition function that defines the number of partitions the table will have and how the boundaries of the partitions are defined. After all these pieces are in place, it is possible to use metadata-only operations to load and remove data from the partitioned table almost immediately.

Partitioning a SQL Server database table is a three-step process

1.    Partition function

2.    Partition scheme

3.    Partition the table

Creating the Partition function

We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function.

CREATE PARTITION FUNCTION cust_part_func (int)

 AS RANGE RIGHT

 FOR VALUES (250000, 500000, 750000)

These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.

Notice that the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than ]or equal to 250,000; the second partition would have included values between 250,001 and 500,000, and so on.

Creating the Partition scheme

This process is linking the partitions to filegroups.

if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

CREATE PARTITION SCHEME cust_part_scheme

 AS PARTITION cust_part_func

 TO (fg1, fg2, fg3, fg4)

Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.

Creating the Partition the table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

CREATE TABLE cust

            (FName nvarchar(40),

             LName nvarchar(40),

             CNum  int)

 ON cust_part_scheme (CNum)

Create Portion Table [ All together ]

CREATE PARTITION FUNCTION cust_part_func (int)

AS RANGE RIGHT

FOR VALUES (250000, 500000, 750000)

GO

CREATE PARTITION SCHEME cust_part_scheme

AS PARTITION cust_part_func

TO (fg1, fg2, fg3, fg4)

GO

 CREATE TABLE cust

            (FName nvarchar(40),

             LName nvarchar(40),

             CNum  int)

 ON cust_part_scheme (CNum)

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS