SQL Server表分区删除详情

一、引言

删除分区又称为合并分区，简单地讲就是将多个分区的数据进行合并。现以表sales.salesorderheader作为示例，演示如何进行表分区删除。

重要的事情说三遍：备份数据库！备份数据库！备份数据库！

二、演示

2.1、数据查询

2.1.1、查看分区元数据

select * from sys.partition_functions       --分区函数
select * from sys.partition_range_values    --分区方案

2.1.2、统计每个分区的数据量

select $partition.salesorderheader_orderdate(orderdate) as number,count(1) as count
from [sales].[salesorderheader]
group by $partition.salesorderheader_orderdate(orderdate)

分区表中有数据时，是不能够删除分区方案和分区函数的，只能将数据先移到其它表中，再删除。

2.2、删除实操

2.2.1、合并原表分区

alter partition function salesorderheader_orderdate() merge range('2011-01-01 00:00:00.000')
alter partition function salesorderheader_orderdate() merge range('2012-01-01 00:00:00.000')
alter partition function salesorderheader_orderdate() merge range('2013-01-01 00:00:00.000')
alter partition function salesorderheader_orderdate() merge range('2014-01-01 00:00:00.000')

2.2.2、备份原表所有索引的创建脚本

alter table [sales].[salesorderheader] add  constraint [pk_salesorderheader_salesorderid] primary key nonclustered 
(
    [salesorderid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

2.2.3、删除原表所有索引

alter table [sales].[salesorderheader] drop constraint [pk_salesorderheader_salesorderid]

2.2.4、创建临时表

create table [sales].[salesorderheader_temp](
    [salesorderid] [int] identity(1,1) not for replication not null,
    [revisionnumber] [tinyint] not null,
    [orderdate] [datetime] not null,
    [duedate] [datetime] not null,
    [shipdate] [datetime] null,
    [status] [tinyint] not null,
    [onlineorderflag] [dbo].[flag] not null,
    [salesordernumber]  as (isnull(n'so'+convert([nvarchar](23),[salesorderid]),n'*** error ***')),
    [purchaseordernumber] [dbo].[ordernumber] null,
    [accountnumber] [dbo].[accountnumber] null,
    [customerid] [int] not null,
    [salespersonid] [int] null,
    [territoryid] [int] null,
    [billtoaddressid] [int] not null,
    [shiptoaddressid] [int] not null,
    [shipmethodid] [int] not null,
    [creditcardid] [int] null,
    [creditcardapprovalcode] [varchar](15) null,
    [currencyrateid] [int] null,
    [subtotal] [money] not null,
    [taxamt] [money] not null,
    [freight] [money] not null,
    [totaldue]  as (isnull(([subtotal]+[taxamt])+[freight],(0))),
    [comment] [nvarchar](128) null,
    [rowguid] [uniqueidentifier] rowguidcol  not null,
    [modifieddate] [datetime] not null
)

2.2.5、更改原表数据空间类型

1）对着原表sales.salesorderheader点击”右键”->”设计”。

2）点击菜单栏”视图”->”属性窗口”。

3）将数据空间类型更改为”文件组”，常规数据空间规范默认为”primary”。

2.2.6、移动原表分区数据到临时表

alter table [sales].[salesorderheader] switch partition 1 to [sales].[salesorderheader_temp] partition 1

2.2.7、创建原表所有索引到临时表

alter table [sales].[salesorderheader_temp] add  constraint [pk_salesorderheader_salesorderid] primary key nonclustered 
(
    [salesorderid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

2.2.8、删除原表

drop table sales.salesorderheader

2.2.9、删除分区方案和分区函数

drop partition scheme salesorderheader_orderdate
drop partition function salesorderheader_orderdate

2.2.10重命名表名

exec sp_rename '[sales].[salesorderheader_temp]','salesorderheader'

到此这篇关于sql server表分区删除详情的文章就介绍到这了,更多相关sql server表分区删除内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！