Hi,

try this:

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

I don't know if it's the best solution, but it works.