Tuesday, July 5, 2011

Table Partition

Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.

Benefits of table partitioning

There are numerous benefits of table partitioning:
  • To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
  • Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
  • Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
  • A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.
 The steps for creating a partitioned table include the following:
  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.
 
Below are the steps to creation Horizontal Table Partition

-- Create the partition function

CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO

-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO

 -- Create a simple table
CREATE TABLE PartitionTable (
   DateKey int NOT NULL,
   CustomerKey int NOT NULL,
   SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
   (DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO

------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows
INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO

-- A query accesses the entire table, exactly as you'd expect.
SELECT * FROM PartitionTable
GO

--Query partition contents
SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO

5 comments:

  1. how to creat partition on existing table and database

    ReplyDelete
  2. Hi Rahul,
    Good to hear a comment! You had posted a very good question. I am giving two approaches to answer your query:

    Approach1:
    -----------
    Create new table using T-SQL code provided in the Post and Load data from existing table to new table. Once data is loaded, Drop existing table and shrink database. You may or may not like this approch.

    Approach2:
    -----------
    1. Alter your database and add new file groups.
    2. Create Partition Function for required Ranges.
    3. Create Partition Scheme and add above file groups.
    4. Create Culstered Index on existing table using above Partition Scheme to move data in partitions.

    However I am planning to post a new title "Table Partitioning to an existing table"

    ReplyDelete
  3. I have a table "AdvAcFinancialDetail" in which i have 25 lacs record

    I have create another thable that have same record as table "AdvAcFinancialDetailPartition"
    I have Created A PartitionFunction and partitioned schema on this table on the "AccountKey" column


    But when i execute a query on both table

    With where Condition
    AC_KEY>7000000 AND AC_KEY<8000000

    Both query takes Same time why?


    as I have created a partion on one table


    Reason

    ReplyDelete
  4. Hi Rishi (Rahul),
    Did you create multiple file groups or datafiles? To find out the actual root cause, I would like to know the Partition Function and Schame you have had implemented on those tables.

    ReplyDelete
  5. NO i just have a primary file group

    ReplyDelete

Here are few FREE resources you may find helpful.