SQL SERVER – 2005 – Introduction to Partitioning

Partitioning is the database process or method where very large tables and indexes are divided in multiple smaller and manageable parts. SQL Server 2005 allows to partition tables using defined ranges and also provides management features and tools to keep partition tables in optimal performance.

Tables are partition based on column which will be used for partitioning and the ranges associated to each partition. Example of this column will be incremental identity column, which can be partitioned in different ranges. Different ranges can be on different partitions, different partition can be on different filegroups, and different partition can be on different hard drive disk to improve performance.

Partitions can be set up very easily using schemes and functions as well can manage very easily humongous tables separate indexes for each partition. This will lead to high performance gain. Partitioning can increase performance when hard disk speed is performance bottleneck, if CPU or RAM is bottleneck partitioning will not help much.

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – Download Microsoft SQL Server 2005 Assessment Configuration Pack
Next Post
SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table

Related Posts

23 Comments. Leave new

  • When do I use vertical partitioning?

    Does vertical partitioning happen one subsystem at a time, one level at a time or if the horizontal partitioning breaks down?

    Thank you.

    Reply
  • chandrasekar
    July 30, 2008 4:41 pm

    hi Pinal,
    i need to know the partition types. ?which partition is better .
    on what basics i should select the partition type to partitioned my table…plz guide me

    Reply
  • hi Pinal,
    Percise and good presentation…
    Below are my doubts.
    1. Can we create partitioning, based on two columns?
    2.Can you specify the significance of index on a partition table?
    3.please let me know URLs for your subsequent articles on Partitioning

    Reply
  • Gerardo Melendrez
    June 6, 2009 4:43 am

    Hi,

    If I want to have some sort of redundant mechanism for reporting purposes (let’s say I have a transactions table that I would like to have duplicated, so transactions are going in real time to the live table, and reports are getting from that other duplicated table) for performance and scalability reasons.

    Is partitioning the table a viable solution?

    If not, would anybody recommend me anything?

    Thanks a lot,

    I will appreciate your help.

    Gerardo Melendrez

    Reply
  • Hi Pinal,
    Can you tell me please if partitioning can help with performance when data is stored on SAN? I think it might be useless, because even if I have different drives, they might reside on the same LUN. What do you think?
    Thanks,
    Alex

    Reply
  • Is it possible to partition a table that already exists? I have a table which is 1/2 terabyte and would like to partition by year, but the table already exists.

    Reply
  • Hi Pinal,

    I’m facing problems that alex and Diana had (the very bottom two comments). Could you please answer those questions for me?

    Thanks a lot.

    Richard

    Reply
  • Hi Pinal,

    Does SQL Server supports vertical partitioning??

    Reply
    • In SQL server the two types of vertical partitioning that are present are normalization and row splitting. Normalization is a standard procedure used in RDBMSs. Row splitting is where the number of columns in one table are split into different tables and connected by their primary key. This improves performances by decreasing the amount of data scanned during querying.

      Reply
  • plz provide me code for vertical partitioning of table

    Reply
  • It is possible to create patition on selected table which contains hugh data, if yes then how it is possible ??

    Reply
  • “Different ranges can be on different partitions, different partition can be on different filegroups, and different partition can be on different hard drive disk to improve performance.”

    Hi Dave, can you answer if there is any mistake in this sentence?

    Reply
    • I think “different partition can be on different hard drive disk to ” should be “different filegroups can be on different hard drive disk”

      Reply
  • Is there enabling disabling native partitioning in SQL Server 2005? I have seen in tables property to set “Table is partitioned” to true or false. However, this is inactive, how can we change this configuration?

    Thank you
    Woin

    Reply
  • Hi Pinal Dave,

    I have a table has ID column with identity primary key column with clustered index. This column not used directly in any where condition. I would like to partition the table based on Date column which is not part of any index and not used in any where condition. Kindly advice me what kind of index is recommanded for Date field. Is partition column required to be have unique records? in this case may column doesn’t has unique records.

    Thanks in Advance,
    Arul

    Reply
  • Hi Pinal,

    Request you to please provide the document for creating the vertical partitioning in a table(pre steps and post steps)

    Regards,
    Karthik G

    Reply
  • Hi pinal,
    When i altering a partition scheme for next file group and partition function for new range value. The new Partition Records are not moving to the new file group as i expected. Instead its moving all the records from primary file group to new file group and the new range values are inserting into the primary file group. And its taking large time to complete this activity. Please guide me the perfect way to reduce the alter partition function and use the file groups as expected.

    Thanks
    Kannadhasan G

    Reply
    • You have to give complete repro via script.

      Reply
      • For Example I have a Table Called Employee.
        Column Names are Employee_PK,First_name,Laste_Name,Designation,Audit_inserted_Date,Audit_Updated_Date.

        Now Currently this Table Have 3000 Records.

        Now I am going to create 3 Partition for this table.

        Note: Already i did created Partition 2 on last year. Now i need to create partition 3.

        The data should be like this
        Partition 1 = 1000 records.—> Primary File group
        partition 2 = 2000 records.—> File Group 1
        Partition 3 = PK values should be > 2000 File Group 2.

        Now Alter my Scheme Like this.

        ALTER PARTITION SCHEME PS_Employee
        NEXT USED [FileGroup2]

        And Function Like This.

        ALTER PARTITION FUNCTION PF_Employee ()
        SPLIT RANGE (2001)

        Now it’s looking good when i check the partitions by below query

        SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
        FROM sys.partitions p
        INNER JOIN sys.objects o ON o.object_id=p.object_id
        INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
        WHERE o.name = ‘Employee’
        order by 2,4

        objectname indexname partition_id partition_number rows
        Employee PK_Index XXXXX 1 1000
        Employee PK_Index XXXXX 2 1500
        Employee PK_Index XXXXX 3 0

        also new records are going to 3 partition as i expected.

        The problem is when i check the file group size and it’s records count per file group that’s looking like below.

        SELECT p.partition_number, fg.name, p.rows
        FROM sys.partitions p
        INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
        INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
        WHERE p.object_id = OBJECT_ID(‘Employee’)
        order by partition_number

        Before partition 3

        partition_number name rows
        1 PRIMARY 1000
        2 FileGroup1 1500

        After partition 3

        partition_number name rows
        1 PRIMARY 1000
        2 FileGroup2 1500
        3 FileGroup1 0

        This my problem partition 2 has moved to filegroup2. So it took More time complete the transfer. And This not what i am expected.

        Currently in my production environment all the tables have more or less 300 MB of Size. So it is not good to create partition with this problem, because it is need more down time.

        Please Advise me for reduce the time and stop moving file groups. And i expecting Like

        Partition 1 —> Primaryfilegroup
        Partition 2 —> filegroup2
        Partition 3 —> Filegroup3

        Thanks
        Kannadhasan G

  • For Example I have a Table Called Employee.
    Column Names are Employee_PK,First_name,Laste_Name,Designation,Audit_inserted_Date,Audit_Updated_Date.

    Now Currently this Table Have 3000 Records.

    Now I am going to create 3 Partition for this table.

    Note: Already i did created Partition 2 on last year. Now i need to create partition 3.

    The data should be like this
    Partition 1 = 1000 records.—> Primary File group
    partition 2 = 2000 records.—> File Group 1
    Partition 3 = PK values should be > 2000 File Group 2.

    Now Alter my Scheme Like this.

    ALTER PARTITION SCHEME PS_Employee
    NEXT USED [FileGroup2]

    And Function Like This.

    ALTER PARTITION FUNCTION PF_Employee ()
    SPLIT RANGE (2001)

    Now it’s looking good when i check the partitions by below query

    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
    FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id=p.object_id
    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
    WHERE o.name = ‘Employee’
    order by 2,4

    objectname indexname partition_id partition_number rows
    Employee PK_Index XXXXX 1 1000
    Employee PK_Index XXXXX 2 1500
    Employee PK_Index XXXXX 3 0

    also new records are going to 3 partition as i expected.

    The problem is when i check the file group size and it’s records count per file group that’s looking like below.

    SELECT p.partition_number, fg.name, p.rows
    FROM sys.partitions p
    INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
    WHERE p.object_id = OBJECT_ID(‘Employee’)
    order by partition_number

    Before partition 3

    partition_number name rows
    1 PRIMARY 1000
    2 FileGroup1 1500

    After partition 3

    partition_number name rows
    1 PRIMARY 1000
    2 FileGroup2 1500
    3 FileGroup1 0

    This my problem partition 2 has moved to filegroup2. So it took More time complete the transfer. And This not what i am expected.

    Currently in my production environment all the tables have more or less 300 MB of Size. So it is not good to create partition with this problem, because it is need more down time.

    Please Advise me for reduce the time and stop moving file groups. And i expecting Like

    Partition 1 —> Primaryfilegroup
    Partition 2 —> filegroup2
    Partition 3 —> Filegroup3

    Thanks
    Kannadhasan G

    Reply

Leave a Reply

Menu