Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partition Function to an existing table Expand / Collapse
Author
Message
Posted Friday, August 28, 2009 11:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Thursday, August 25, 2011 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 6, 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
Posted Thursday, August 25, 2011 10:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Friday, August 26, 2011 8:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 4,321, Visits: 6,115
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 »

Add to briefcase

Permissions Expand / Collapse