Partition Function to an existing table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Partition Function to an existing table

Partition Function to an existing table

Rate Topic

Display Mode

Topic Options

Author

Message

MTY-1082557

Posted Friday, August 28, 2009 11:13 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, Visits: 502

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

Post #779242

J Hines

Posted Thursday, August 25, 2011 9:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 06, 2012 12:21 PM
Points: 29, Visits: 1,254

This is EXACTLY what I am trying to do, only by month rather than week. Did you ever get help with this?

- JH

Post #1165451

jomar.imbat

Posted Thursday, August 25, 2011 10:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, March 18, 2013 9:01 AM
Points: 75, Visits: 70

Please take this for an example:
CREATE PARTITION FUNCTION [myPF1] (datetime)
AS RANGE LEFT FOR VALUES ('20110731', '20110807', '20110814', '20110821');
CREATE PARTITION SCHEME myPS2
AS PARTITION myPF1
TO ( fg1, fg2, fg3, fg4 );

This should be what will each filegroup(fg) contain:
FG1= DATA <=20110731
FG2= DATA > 20110731 AND DATA <= 20110807
FG3= DATA > 20110807 AND DATA <= 20110814
FG4= DATA > 20110821

You can also modify the partition scheme to include a next filegroup. hope this helps.

Post #1165536

TheSQLGuru

Posted Friday, August 26, 2011 8:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 3,572, Visits: 5,106

J Hines (8/25/2011)

This is EXACTLY what I am trying to do, only by month rather than week. Did you ever get help with this?

1) Please don't post a "me too" to a 2 year old thread - start a new one.

2) Partitioning is a VERY advanced topic where you can get yourself into trouble (and even make things much worse) if you don't know what you are doing. PLEASE do yourself a favor and get some professional help for a little bit to make sure you know what you really need to do and how to properly implement/test/maintain it.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #1166083

« Prev Topic | Next Topic »

Permissions