Windows LiveWindows Live™
 
 
jose's profileSQL Server 2005 blogsBlogLists Tools Help

Blog
    February 28

    Introduction to Table Partitioning

    Introduction to Table Partitioning

    In SQL Server 2005

    Table partitioning is one of Scalability and maintainability features of SQL Server 2005. With these feature a table can be spread across multiple filegroups. It also allows maintenance (such backup and restore) operations to be performed only on subsets of data (partition) rather than the whole table.

    Some partitions in a table can be mapped to a read-only filegroup while other partitions can be mapped to a read/write enabled filegroups.These feature can be used as security implementation to protect subset of sensitive data residing in the same table. Consider a case of a Sales transaction table which contains sales transaction from the present and previous years. Those transactions from the previous years (partition) can be mapped to a readonly filegroup while the present sales transaction can be mapped on a read/write filegroups thereby protecting previous records from being altered irregardless of whoever has logged-in to the system provided he/she did not modify the filegroups readonly attribute

    Although the data can be spread across multiple files and filegroups it is still treated as a single table in the datbase

     

    Requirements for table partitioning:

    1. A partition function - which defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.

    2. A partition scheme – which maps each partition specified by the partition function to a filegroup.

    These are the required  steps for creating a partitioned table or partitioned index:

    1. Create a partition function to specify how a table or index that uses the function can be partitioned.

    2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.

     

    3. Create a table or index using the partition scheme.

    To do:

    A. Creating a partition function

    1. Create a new database

    a. Open SQL server 2005 management studio

    b. Connect to your SQL server 2005 database engine

    c. Right click database > new database > enter "mytest" as the new database name. Click ok.

    2. Create the new partition

    a. Click on "new query" on the sql server 2005 management studio. Connect to the database engine and

    b. Type the following

    Use mytestdb

    CREATE PARTITION FUNCTION myRangePF1 (int)

    AS RANGE LEFT FOR VALUES (1, 100, 1000);

    A table that uses this partition function on partitioning column col1 would be partitioned as follows

    A. col1 <= 1

    B. col1 > 1 and <=100

    C. col1 > 100 and <=1000

    D. col1 => 1000

    Notes:

    a. The partition is stored in the database located in the "storage>partition functions" tabs

    b. The scope of a partition function is limited to the database that it is created in. Within the database, partition functions reside in a separate namespace from the other functions.

    c. Any rows whose partitioning column has null values are placed in the left-most partition, unless NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.

    B. Creating A partition scheme

    The partition function above partitions a table bind to it in to 4 partitions as stated above. So a partition scheme for this partition function can be mapped into four different filegroups. For more information about files and filesgroups kindly consult your books online.

    Here’s a sample TSQL statement for creating a partition scheme

    CREATE PARTITION SCHEME myRangePS1

    AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) <-- these are the filegroups

    In this exercise since we created a database with only one file group which is the "primary" filegroup  We will be using the this scripts.

    CREATE PARTITION SCHEME myRangePS1

    AS PARTITION myRangePF1 TO (

    ‘primary’, ‘primary’, ‘primary’, ‘primary’)

    To do:

    1. Open SQL Server 2005 management studio

    2. Connect to SQL server 2005 database engine

    3. Be sure to use

    "mytest" database from the previous exrcise

    4. click on the new query paste the scripts above and press F5 to execute

    You have just created a new partitioning scheme.

    5. Drop the partition scheme and recreate it by using the following scripts below. Notice that there is an excess filegroup in the filegroup names

    Create PARTITION SCHEME myRangePS1

    AS PARTITION myRangePF1 TO (

    ‘primary’, ‘primary’, ‘primary’, ‘primary’, ‘primary’)

    6. SQL server creates the partition scheme and returns the following message:

    Partition scheme 'myRangePS1' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'myRangePS1'.

    As you can see the excess filegroup has been mark for use should a new partition would be created in the future

    C. Create a table that uses the partition

    Use the following scripts to create a partion table

    CREATE TABLE PartitionTable (col1 int, col2 char(10))

    ON myRangePS1 (col1) ;

    1:48 PM | Add a comment | Read comments (6) | Permalink | Blog it