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? 
