[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1245
  • 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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