Systems Engineering and RDBMS

Table Data Partitioning in SQL Server 2005

Posted by decipherinfosys on January 8, 2008

In this blog post, we will cover a working example on data partitioning feature in SQL Server 2005. In subsequent posts, we will cover the different important aspects of data partitioning and the different types of partitioning that are available, their usage scenarios, things to look out for etc..

Prior to SQL Server 2005, DPVs (Distributed Partitioned Views) were used to achieve partitioning. In SQL Server 2005, the data partitioning feature makes it very simple to do this (Oracle and DB2 LUW’s partitioning features have been available since quite some releases).

The comments for each step are inline and explain what each step does.

USE TEST
GO

/*********************************************************************
STEP #1:
Even though this step is not necessary to demonstrate the
partitioning functionality, the very first step would typically
be the creation of separate filegroups.

For the sake of this script, we will skip that part and keep
everything in the same filegroup.

Let us add a filegroup to this database and add two files to it.
**********************************************************************/

ALTER DATABASE TEST ADD FILEGROUP FLG_1;
GO

ALTER DATABASE TEST
ADD FILE
(
NAME = TEST_DAT_1,
FILENAME = ‘C:\TEST_DAT_1.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = TEST_DAT_2,
FILENAME = ‘C:\TEST_DAT_2.ndf’,
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP FLG_1;
GO

/*********************************************************************
STEP #2:
The second step in the process is to create a partitioning function
What the script below does is that it creates range partitions
and since 3 values are used, 4 ranges of values are created:

Partition #1: All values < 100000
Partition #2: All values >=100000 and < 500000
Partition #3: All values >=500000 and < 1000000
Partition #4: All values > 1000000
**********************************************************************/
CREATE PARTITION FUNCTION PARTITION_FN_1 (BIGINT)
AS RANGE LEFT FOR VALUES (100000, 500000, 1000000);

/*********************************************************************
STEP #3:
The third step in the process is to create the partition scheme.
This is the step where we map the partitions to filegroups.

Since we are using the same filegroup, we will map all of them to
FLG_1 filegroup. We can also use the “ALL” keyword in such a scenario
instead of specifying the same filegroup 4 times.
**********************************************************************/

CREATE PARTITION SCHEME PARITION_SCHEME_1
AS PARTITION PARTITION_FN_1 TO (FLG_1, FLG_1, FLG_1, FLG_1);
GO

OR

CREATE PARTITION SCHEME PARITION_SCHEME_1
AS PARTITION PARTITION_FN_1 ALL TO (FLG_1);
GO

/*********************************************************************
STEP #4:
The final step in this process is to create the table using the
scheme that we created in Step #3.
**********************************************************************/

CREATE TABLE TEST_PARTITION
(
COL1 BIGINT IDENTITY,
COL2 NVARCHAR(10),
CONSTRAINT PK_TEST_PARTITION PRIMARY KEY (COL1)
) ON PARITION_SCHEME_1 (COL1);
GO

Now that we have created the table that way, once we enter the data into this table, it will automatically go into the right partition. Let’s use a script to test this out quickly.

SET NOCOUNT ON
GO
DECLARE @I INT
SET @I = 1
WHILE (@I <= 1000001)
BEGIN
SET IDENTITY_INSERT TEST_PARTITION ON
INSERT INTO TEST_PARTITION (COL1, COL2) VALUES (@I, ‘X’);
SET IDENTITY_INSERT TEST_PARTITION OFF
SET @I = @I + 100000
END
GO

And now, let’s see the data distribution:

SELECT $PARTITION.PARTITION_FN_1 (COL1) AS PARTITION_NBR, COUNT(*) AS CNT_RECORDS
FROM dbo.TEST_PARTITION
GROUP BY $PARTITION.PARTITION_FN_1 (COL1)
ORDER BY PARTITION_NBR ;

PARTITION_NBR    CNT_RECORDS

————-     ———–

1                          1

2                          4

3                          5

4                          1

And by the default behavior, the index(es) that are created on a partitioned table will also use the same partitioning scheme and the partitioning column. In future blog posts, we will dig deeper into partitioning in SQL Server 2005, the splitting, merging, switching of partitions as well as the performance implications and query statements for querying the meta-data.

One Response to “Table Data Partitioning in SQL Server 2005”

  1. […] Table Data Partitioning in SQL Server 2005 […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: