Table with 120 Million rows in SQL2005. This table grows around 400, 000 rows everyday. Insertion is made by a package every morning. In order to improve performance in the insertion into the table, I’m planning to do a partition. Table has clustered index (id, date).
What's the best partition function for this case????
Here is what I'm trying to do:
-- partition function
CREATE PARTITION FUNCTION function (date)
AS RANGE LEFT
FOR VALUES (week1, week2, week 3) ----> what I should write exactly in "week1"....
-- partition scheme
CREATE PARTITION SCHEME scheme
AS PARTITION function
ALL TO (week1, week2.....) -- file groups will be located in different drives
-- move existing table/data to new partition
ALTER TABLE [table] DROP CONSTRAINT PK_constraint WITH (MOVE TO [scheme] (Date))
note: how I move the data to the exact week ????? am I using the correct syntax?
ALTER TABLE [table] ADD CONSTRAINT PK_Constraint PRIMARY KEY(ID, Date)
I hope to hear your advices