Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partition Function to an existing table


Partition Function to an existing table

Author
Message
MTY-1082557
MTY-1082557
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 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
J Hines
J Hines
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 1254
This is EXACTLY what I am trying to do, only by month rather than week. Did you ever get help with this?


- JH
jomar.imbat
jomar.imbat
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 73
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.
TheSQLGuru
TheSQLGuru
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9348 Visits: 8483
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 on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search