--- Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (datetime)
AS RANGE LEFT FOR
VALUES ('2010-10-21');
GO

--- Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], [Secondary]);
GO

--Create table on partition scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME
)ON TestDB_PartitionScheme (Date);
GO

CREATE CLUSTERED INDEX [IX_TestTable] ON TestTable([ID] ASC, [Date] ASC) ON TestDB_PartitionScheme(Date)
GO

ALTER TABLE TestTable ADD CONSTRAINT [pc_work_PK_new] PRIMARY KEY ([ID] ASC ) ON [PRIMARY]
GO

--- Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,'2010-10-20');
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,'2010-10-21');
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,'2010-10-22');
GO


--- Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO