How to partition an existing table into a few partitions

• Hooman Shamsborhan

  Old Hand

  Points: 396

  More actions

  December 21, 2008 at 5:40 pm

  #129339

  Hi Guys

  I have a very huge existing table, called 'Transaction_Summary', with 82 columns!!! containing more that 100 mil records and because search queries get really slow and in order to make the quicker I have to partition table.

  The partition boundary will be a non-primary key CHAR(4) field called "Channel". In order to partition this table I've done the following steps:

  I created 5 partitions for my table as the following code:

  CREATE PARTITION FUNCTION ParitioningTest_PartitionRange(nchar(4)) AS RANGE LEFT FOR VALUES ('CARD', 'CBO', 'CHEQ', 'CUST', 'TELL')

  CREATE PARTITION SCHEME ParitioningTest_PartitionScheme AS PARTITION ParitioningTest_PartitionRange TO ([FG_CARD], [FG_CBO], [FG_CHEQ],[FG_CUST], [FG_TELL], [FG]);

  Now I dont know how to move existing records from 'Transaction_Summary' table to this partitions.

  I should mention that all this partitioning is for this "Transaction_Summary" which has more than 100 million records and there in a CHAR(4) field in this table called "Channel" which doesnt have an index(there is an id column which is primary key" and I want to do the partitioning based on this filed, I know if that filed was a primary key I could say Drop index Transaction_Summary_IXC on Transaction_Summary with (Move To [ParitioningTest_PartitionScheme] (Channel) ) But I dont know what to do in this case, could you please help me?

  Regards,

  Hooman

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 21, 2008 at 6:22 pm

  #915920

  This is a duplicate entry. Please deo not cross-post. Direct all replies to this thread: http://www.sqlservercentral.com/Forums/Topic623669-360-1.aspx

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]