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 --分区方案
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
表分区删除内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
上一篇: 红外线额温枪模块硬件结构
下一篇: SQL查询语句求出用户的连续登陆天数
推荐阅读