Partitioning Example

The following code will walk you through the process of creating a partitioned table:

--------------------------------
-- Create A Partitioned Table --
--------------------------------
 
/* Create a partition function. */
Create Partition Function [test_monthlyDateRange_pf] (smalldatetime) 
    As Range Right For Values 
    ('2008-01-01', '2008-02-01', '2008-03-01');
Go
 
/* Associate the partition function with a partition scheme. */
Create Partition Scheme test_monthlyDateRange_ps 
    As Partition test_monthlyDateRange_pf 
    All To ([Primary]);
Go
 
/* Create your first partitioned table! 
   Make sure the data types match.  */
Create Table dbo.orders
(
      order_id  int Identity(1,1)   Not Null
    , orderDate smalldatetime       Not Null
 
    Constraint PK_orders Primary Key Clustered
    (
        orderDate
      , order_id
    )
) On test_monthlyDateRange_ps(orderDate);
Go
 
/* Create some records to play with. */
Insert Into dbo.orders
Select '2007-12-31' Union All
Select '2008-01-02' Union All
Select '2008-01-03' Union All
Select '2008-01-04' Union All
Select '2008-02-01' Union All
Select '2008-02-02' Union All
Select '2008-03-01' Union All
Select '2008-03-02';
 
/* The $partition function can be used to interrogate partition data.
   Let's use it to see where those records are physically located. */
Select $partition.test_monthlyDateRange_pf(orderDate) 
    As 'partition_number'
    , *
From dbo.orders;
 
/* By default, all new indexes are created on the partition.
   Let's create an aligned index */
Create NonClustered Index IX_orders_aligned
    On dbo.orders(order_id)
    On test_monthlyDateRange_ps(orderDate);
 
/* Now let's create an un-aligned index.
   We'll need to specify the filegroup. */
Create NonClustered Index IX_orders_nonaligned
    On dbo.orders(order_id)
    On [Primary]; -- can be any filegroup
 
/* Review your indexes */
Execute sp_helpindex orders;

Using the previous code as a building block, let’s try swapping partitions:

--------------------------
-- Swap Out A Partition --
--------------------------
 
/* We need to drop our un-aligned index; otherwise we'll 
   get an error when we attempt to do the switch. */
Drop Index IX_orders_nonaligned On dbo.orders;
 
/* Create the table to hold the data you're swapping out.
   The table structures must match identically; however, 
   DO NOT partition this table. */
Create Table dbo.orders_stage_swapOut
(
      order_id  int             Not Null
    , orderDate smalldatetime   Not Null
 
    Constraint PK_orders_stage_swapOut Primary Key Clustered
    (
        orderDate
      , order_id
    )
) On [Primary];
Go
 
/* Create the table to hold the data you're swapping in.
   The table structures must match identically; however,
   DO NOT partition this table. */
Create Table dbo.orders_stage_swapIn
(
      order_id  int             Not Null
    , orderDate smalldatetime   Not Null
 
    Constraint PK_orders_stage_swapIn Primary Key Clustered
    (
        orderDate
      , order_id
    )
) On [Primary];
Go
 
/* Populate the table you're swapping in. */
Insert Into dbo.orders_stage_swapIn
Select -5, '2008-02-02' Union All
Select -4, '2008-02-03' Union All
Select -3, '2008-02-04' Union All
Select -2, '2008-02-05' Union All
Select -1, '2008-02-06';
 
/* Create any indexes on your table to match the 
   indexes on your partitioned table. */
Create NonClustered Index IX_orders_stage_swapIn
    On dbo.orders_stage_swapIn(order_id);
 
/* Add a check constraint for the 
   partition to be swapped in. 
   This step is required. */
Alter Table dbo.orders_stage_swapIn
With Check
Add Constraint orders_stage_swapIn_orderDateCK
	Check (orderDate >= '2008-02-01' 
		And orderDate < '2008-03-01');
Go
 
/* Swap out the old partition. */
Alter Table dbo.orders 
    Switch Partition 3 To dbo.orders_stage_swapOut;
Go
 
/* Swap in the new partition. */
Alter Table dbo.orders_stage_swapIn
    Switch To dbo.orders Partition 3;
Go
 
---------------------
-- Check your data --
---------------------
 
/* You should have 2 records in here. */
Select * From dbo.orders_stage_swapOut;
 
/* You should have 5 records here. */
Select * 
From dbo.orders
Where orderDate >= '2008-02-01' 
And orderDate < '2008-03-01';
 
/* There should be no records in this table. */
Select * From dbo.orders_stage_swapIn;
 
Select $partition.test_monthlyDateRange_pf(orderDate) 
    As 'partition_number'
    , *
From dbo.orders;
 
/* Clean-up time!
Drop Table dbo.orders
Drop Table dbo.orders_stage_swapOut
Drop Table dbo.orders_stage_swapIn
Drop Partition Scheme test_monthlyDateRange_ps
Drop Partition Function [test_monthlyDateRange_pf]
*/

Pretty easy, huh? :)

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

7 Responses to Partitioning Example

  1. Brian Robbins says:

    /* Associate the partition function with a partition scheme. */
    CREATE Partition Scheme test_monthlyDateRange_ps
    AS Partition test_monthlyDateRange_pf
    All TO ([PRIMARY]);

    This seems to be similar to instantiating an object in OOP .NET…. variableOne ClassType = new ClassType….

  2. In terms of the actual code, it’s pretty similar. However, what’s occurring behind the scenes is pretty different. Associating a partitioning function with a partitioning scheme affects where data is physically stored; instantiation, on the other hand, creates an object in memory.

  3. Luis Simoes says:

    If we switch out/in data to temporary tables using the same partition function can it be faster or using primary with constraints is the fastest method?

    Regards

  4. Meg says:

    if i set a filegroup to readonly.. what’s with index reorg? … if parts of the index is in the readonly filegroup

    Regards Meg

  5. Murali says:

    Hi ,

    We are planning to implement sliding window partiontion on date range in our environment. We have to retain 3 yrs worth of data in the DB. we decided to create partions based on monthly basis. There are 720 tabes. How can I automate this process for all the tables at the end of each month.

    thanks in advance

    Regards
    Murali

  6. Yuki says:

    how to partitiion the table based on day? one month has the most 31 days, please show us the way how to create 31 partition as the field we collected in the table does not have a computed day column, what we have is just the insert date.
    I read in the white paper, if we can partition the table to day, we can use the ring structure and the new data rotates automatically. Further to this, i have one question, if this is the case, can we just truncate the table of one partition as we intend to keep the data in the staging server for just for one month? For example, we create a store procedure to check for current month max day and implement the trucate first partition a day before the max day. For Example:

    Current Month: Nov, max day 30 days
    and when the current month is reaching (max -1 day), we truncate the data in the 1st partition( which contains 1 Nov data only & get this table ready for 1st Dec).

  7. Pingback: Are You Approaching Your Partition Range Limits? « SQL Fool

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>