[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1260
  • Last Modified:

Drop Partition | SQL Server 2008 R2 EE

Experts -

I've a table with Daily Partitions with few million rows. I would like to roll off the old partitions as the new one comes in (14 days window).

How do I drop the old partitions? I've created the partitions till year 2011. Currently my worry is to just write a script to drop the old partitions.

I've done oracle partitions and it seems SQL partitions looks complicated to me with partition functions and schemas.

Any help appreciated.

Thanks,

~Sve
0
sventhan
Asked:
sventhan
  • 2
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
sventhanAuthor Commented:
Thanks angel eyes.

In oracle I issue something like this to drop a single  partition

ALTER TABLE tablename DROP PARTITION partition_name;

Is there anything equivalent for this in SQL server? Do I've to modify the partition function/Scheme to drop the partitions?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Do I've to modify the partition function/Scheme to drop the partitions?

yes
0
 
willz123Commented:
Hi

I guess you want to merge your old partitions into an archive table? You can merge them by merging the current bounday. You can also move date by using the switch command eg

alter partition function YearPF() merge range('01 july 2010')
alter table table1 partition 1 switch to table2

I have found this article quite useful for partition examples:
http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx#sql2k5parti_topic11
0
 
sventhanAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now