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

--------------------------
-- 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]
*/