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? 🙂
/* 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….
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.
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
if i set a filegroup to readonly.. what’s with index reorg? … if parts of the index is in the readonly filegroup
Regards Meg
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
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).
Pingback: Are You Approaching Your Partition Range Limits? « SQL Fool
Thank you for this tutorial! I have never used partitions before and this hands-on example was very helpful in understanding it!
Hi.
This the best example I have seen so far but I do not the Swapout and swapin concept.
Great example!
Thanks.
This is a great article but I do not get the swap out swap in concept.
Users will be entering the data directly and not from a staging table.
Thanks!
Please change my name to a nickname.
Thanks.