Partitioning Table in SQL Server 2005
Partitioning is a new functionality in SQL Server 2005. It lets you split your tables to several filegroups, so user can access your table faster. Partition acts like an object, so you can assign it to your destination table.
You can create partition function by using this query :
CREATE PARTITION FUNCTION partition_fn_name (input_parameter_type)
AS RANGE [LEFT | RIGHT]
FOR VALUES ([ boundary_value ]) [;]
The boundary_value lets you to control how many rows in your table to be separated. Below is illustration for this query :
CREATE PARTITION FUNCTION pFunc (int)as
RANGE LEFT FOR VALUES (100,200);
It means you create partition with LEFT RANGE, your partition will be : infinity to 100, 101-200, and 201 to infinity. If you specify RIGHT range, then your partition will be like this : infinity to 99, 100-199, and 200 to infinity. After you create partition function, you must create a partition scheme to defines the physical storage structures or filegroups that have been made before.
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
TO ( [ filegroups_name ] ) ( ; )
Notes that if you make 2 boundary_value, the result will be 3 partitions, so you must specify 3 filegroups. If you want to use only one filegroups, you can use ALL TO clause rather than TO clause. You can use this partition function and scheme by using query like this :
CREATE TABLE KTP
(KTPID int identity(1,1) PRIMARY KEY CLUSTERED,
NamePenduduk varchar(50) not null,
Address varchar(50) not null)
ON partition_scheme_name(KTPID);
In above example, you put partition scheme and function on KTP ID field in Table KTP, since it has index. You also can put partition function on different tables or fields. You also must make filegroups' used by partition scheme before execute this query. I said before that you can increase your database server performance by creating partitions, since you can put your filegroups to the best hardware that have the fastest READ/WRITE time. For example RAID 5.
Regards,
Rangga Praduwiratna