This article is intended in looking into the Partitioning feature in the SQL SERVER 2005. We have seen in the past versions about the partitioned views.
Beginning with let us see the use of Partitions. Basically partitions help in improving the performance, scalability and managing of the large tables. As the table grows larger and larger the performance in accessing the data is affected, scalability and managing issues arises. With the help of partitioning a table we can achieve a great level of performance and managing of tables.
Let us see in how we can create partitioned tables in detail. The procedure for creating a partitioned table is as follows.
1) Creation of Filegroups
Beginning with this we have to have various filegroups for the database if we need to place the partitioned tables on different filegroups. To create a filegroup, there are different ways one is using the Alter command and the second is using the Interface. Let us see each of them
a) Using the Alter Command
The syntax is as follows
ALTER DATABASE <Database Name> ADD FILEGROUP <Filegroup name>
For eg:
Alter Database TestDB ADD FILEGROUP FG1
After adding a filegroup we need to add files to the filegroup. We can add one or more files. The syntax is as follows
ALTER DATABASE <Database Name>
ADD FILE
(
NAME = <File Name>,
FILENAME = <Path>,
SIZE = <Size>,
MAXSIZE = <Size>,
FILEGROWTH = <Size>
)
TO FILEGROUP <Filegroup Name>;
For eg:
ALTER DATABASE TestDB
ADD FILE
(
NAME = FILE1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FILE1.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB
)
TO FILEGROUP FG1;
b) Using the Interface
The following steps show how we can create using the interface
a) Login to the Microsoft SQL SERVER Management Studio
b) Select the Database and right click and select properties.
c) Select the Filegroup section and add the necessary details and click the add button.
d) Next we will click on the Files section and add a new file and associate this file to the FG2 filegroup .
e) Then finally click on the OK button.
This is procedure for create a filegroup using the interface.
2) Creation of Partition Function
The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. The syntax is as follows
CREATE PARTITION FUNCTION <Function Name> (<Data Type>)
AS
RANGE LEFT/RIGHT FOR VALUES (<value1>,<value2>,…)
For eg:
CREATE PARTITION FUNCTION PFun(int)
AS
RANGE LEFT FOR VALUES(10,50,100)
This creates a partitions function with ranges for an integer column having thresholds 10, 50, 100. So this indicates there would be three partitions where first partition would be less than equal to 10, the second between 11 and less than equal to 50, the third one between 51 and less than equal to 100.
3) Creation of Partition Scheme
The next step is to create the partition scheme after the partition function is created. This is needed for associating the partitions to a specific filegroups.
The syntax is as follows
CREATE PARTITION SCHEME <Partition Scheme Name>
AS
PARTITION <Partition Function Name>
TO (<Filegroup1>,<Filegroup2>,….)
For eg:
CREATE PARTITION SCHEME PScheme
AS
PARTITION PFun TO (FG1,FG2,FG3,[PRIMARY])
If all reside on the same filegroup then we can write as
CREATE PARTITION SCHEME PScheme
AS
PARTITION PFun ALL TO (FG1)
4) Creation of Partition Table
The next step is to create the partitioned table which would be associated with the defined partition scheme.
The syntax is as follows
CREATE TABLE <Table Name>
(
<Column Name1> <Datatype>,
<Column Name2> <Datatype>,
……..
)
ON <Partition Scheme Name> ( <Column Name>)
For eg:
CREATE TABLE EMP
(
EMPNO INT,
ENAME VARCHAR(20),
AGE INT,
)
ON PScheme(EMPNO)
The above four steps clearly states how to create a partitioned table.
Querying a Partitioned Table
After the creation of a partitioned table, now let us see how to query the data from the partitioned tables.
Let us see the various cases in querying
a) Querying the data from a particular partition
Syntax
SELECT <Column Name1>…/* FROM <Table Name> WHERE $PARTITION.<Partition Function Name>(<Column Name>) = <Partition Number>
The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2 and so on.
For Eg:
SELECT * FROM EMP WHERE $PARTITION.PFun(EMPNO) = 1
This returns all the records in the partition 1.
b) Querying for Knowing the Partition Number
Syntax
SELECT $PARTITION.<Partition Function Name>(<Column Name>) = <Partition Range Value>
For Eg:
SELECT $PARTITION.PFun(100)
This returns 3 as the partition number for pratition range >50 and less than equal to 100 .
c) Querying to find the count of records in each partition
Syntax
SELECT $PARTITION.<Partition Function Name>(<Column Name>), COUNT(*) FROM <Table Name> GROUP BY $PARTITION.<Partition Function Name>(<Column Name>)
For Eg:
SELECT $PARTITION.PFun(EMPNO) AS Partition, COUNT(*) AS [COUNT] FROM EMP
GROUP BY $PARTITION.PFun(EMPNO)
ORDER BY Partition ;
This returns the number of records in each partitions.
SPLITTING OF PARTITION
The partitions can be split by splitting the partition ranges. The splitting is done by using the alter partition command. We have to note that before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION <Partition Function Name> () SPLIT RANGE (<PARITITION RANGE VALUE>)
For Eg:
ALTER PARTITION FUNCTION PFun() SPLIT RANGE(75)
This would create a new partition range between >75 and less than equal to 100.
MERGING OF PARTITION
The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater partition range value into a singe partition. This is as shown below.
SYNTAX
ALTER PARTITION FUNCTION <Partition Function Name> () MERGE RANGE (<PARITITION RANGE VALUE>)
For Eg:
ALTER PARTITION FUNCTION PFun() MERGE RANGE(50)
This returns a new merged partitions for 50 and 75 . So now there would be only 10, 75, 100 as the new range values for partitions.
ALTERING PARTITION SCHEME
The partition scheme is altered to add new filegroup which may be required when partitions are splitted.
SYNTAX
ALTER PARTITION SCHEME <Partition Scheme Name> NEXT USED <Filegroup Name>
For Eg:
ALTER PARTITION SCHEME PScheme NEXT USED FG4
This associates a new filegroup FG4 to the partition scheme. The filegroup has to be created by the same steps as mentioned earlier.
DROPPING OF PARTITION
Let us see now how to drop partitions .
a) Drop a Partition Function
To drop a partition function it should not have any partition scheme associated with it.
Syntax
DROP PARTITION FUNCTION <Partition Function Name>
For Eg:
DROP PARTITION FUNCTION PFun
This would drop the partition function.
b) Drop a Partition Scheme
To drop a partition scheme it should not have any table associated with it.
Syntax
DROP PARTITION SCHEME <Partition Scheme Name>
For Eg:
DROP PARTITION SCHEME PScheme
Partition Function Information
The Partition Function Information is obtained from the sys.partition_functions table as shown below
SELECT * FROM SYS.PARTITION_FUNCTIONS
Partition Range Information
The Partition Range information is obtained from the sys.partition_range_values as shown below
SELECT * FROM SYS.PARTITION_RANGE_VALUES
Partition Scheme Information
The Partition Scheme information is obtained from the sys.partition_scheme as shown below
SELECT * FROM SYS.PARTITION_SCHEME
Partitions Information
The Partition information can be obtained from the sys.partitions table as shown below.
SELECT * FROM SYS.PARTITIONS
This is in brief regarding on the Partitioning features in SQL SERVER 2005.