Horizontal Partitioning Strategy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Horizontal Partitioning Strategy

Author

Topic

rbarlow
Starting Member

26 Posts

Posted - 2009-08-28 : 13:49:15

We are planning on upgrading our data warehouse from SQL 2000 to SQL 2008. There is currently a large fact table that is partitioned into separate tables on a monthly basis with a view to join them. There are currently 2 filegroups on separate drives, one for the 4 most current months and another for all the rest.

I want to implement this in SQL 2008 using the built-in partitioning capability. I've got it working with 1 filegroup but having problems implementing it with 2 filegroups. I've figured out how to remove a partition on one end and add a new one on the other, but don't know how to move a partition in the middle to the other filegroup.

Ex: (FG = filegroup)

Before After
====== =====
Sep - FG1
Aug - FG1 Aug - FG1
Jul - FG1 Jul - FG1
Jun - FG1 Jun - FG1
May - FG1 May - FG2 <- change filegroup
Apr - FG2 Apr - FG2
Mar - FG2 Mar - FG2
Feb - FG2 Feb - FG2

Does anybody have any suggestions?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-30 : 09:09:12

I'm not entirely clear on what you're after, but think this may help:

ALTER PARTITION SCHEME MyRangePS1 NEXT USED MayFG2;
ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (boundary_value));

Look it up in BOL.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources