欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server表分区删除详情

程序员文章站 2022-06-17 19:06:10
目录一、引言二、演示2.1、数据查询2.1.1、 查看分区元数据2.1.2、统计每个分区的数据量2.2、删除实操2.2.1、合并原表分区2.2.2、备份原表所有索引的创建脚本2.2.3、删除原表所有索...

一、引言

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

重要的事情说三遍:备份数据库!备份数据库!备份数据库!

二、演示

2.1、数据查询

2.1.1、 查看分区元数据

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

SQL Server表分区删除详情

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点击"右键"->"设计"。

SQL Server表分区删除详情

2)点击菜单栏"视图"->"属性窗口"。

SQL Server表分区删除详情

3)将数据空间类型更改为"文件组",常规数据空间规范默认为"primary"。

SQL Server表分区删除详情

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表分区删除内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!