Partition Function to an existing table

• MTY-1082557

  SSCarpal Tunnel

  Points: 4540

  More actions

  August 28, 2009 at 11:13 am

  #141123

  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

  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

  ADD CONSTRAINT PK_Constraint PRIMARY KEY(ID, Date)

  I hope to hear your advices

• J Hines

  Old Hand

  Points: 385

  More actions

  August 25, 2011 at 9:14 am

  #1373238

  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

  SSC Enthusiast

  Points: 161

  More actions

  August 25, 2011 at 10:14 am

  #1373313

  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

  SSC Guru

  Points: 134017

  More actions

  August 26, 2011 at 8:13 am

  #1373783

  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