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 (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – 2005 – Introduction to Partitioning

  1. Pingback: SQL SERVER - 2005 - Database Table Partitioning Tutorial - How to Horizontal Partition Database Table Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER - 2005 - Database Table Partitioning Tutorial - How to Horizontal Partition Database Table Journey to SQL Authority with Pinal Dave

    • Hi Pinal
      Could you please help me?

      I have a test table .it has a olumn named PersonelImage,and it keeps images of all the personel.because of the larg number of records in table and the large size of the image files,i want to use partitioning and keep the images in another drive.
      Is it true to use vertical partitioning?
      I create a new filegroup called image and placed it in another drive,but i dont know how to create a vertical partition?!

      I know the usage of horizental partitioning and the script for creating it but not about vertical partitioning.

      Like

  3. 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.

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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

    Like

  7. 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

    Like

  8. 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.

    Like

  9. 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

    Like

    • 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.

      Like

  10. “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?

    Like

  11. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s