SQL Server利用分区进行归档

在数据库上有个业务，是将日志表每月归档，归档采用的移动分区的方式，做这个事情的同事现在离职了，现在做了一次数据库升级下面是熟悉的过程

-- 查看分区函数
select * from sys.partition_functions
-- 查看分区方案
select * from sys.partition_schemes

-- 查看数据空间
select * from sys.data_spaces where data_space_id=65605

 -- 查看分区对应的数据空间
--   partition_scheme_id 分区方案ID  ;  destination_id 分区ID,新建一个新的分区，递增1
select * from sys.destination_data_spaces where partition_scheme_id=65605 and destination_id=3

-- 查看分区的边界 function_id 是分区函数的id 
select * from sys.partition_range_values where function_id=65541

-- 查看每个分区的数据
select $partition.pf_testOrder(OrderDate) as Patition,COUNT(*) countRow from dbo.testOrder 
group by $partition.pf_testOrder(OrderDate)

-- 归档数据，将莫一个表的5分区 迁移到 归档表的5分区
alter table testOrder switch partition 5 to testOrdersHistory partition 5
 
-- 用于指定新分区在哪个文件组
ALTER PARTITION SCHEME ps_testOrder NEXT USED  Group3
-- 创建新分区
ALTER PARTITION FUNCTION pf_testOrder() SPLIT RANGE ( '2021-01-03 10:16:18.633') 
 
-- 创建分区函数
CREATE PARTITION FUNCTION pf_testOrder (datetime) AS RANGE RIGHT FOR VALUES ('2020-05-02 10:16:18.633', '2020-06-02 10:16:18.633')
-- 创建分区方案
create partition scheme ps_testOrder as partition pf_testOrder to (Group1,Group2,Group3)


 
 
create table testOrder
(
	OrderID int identity(10000,1),
	OrderDate datetime not null,
	CustomerID int not null,
	constraint PK_Orders primary key(OrderID,OrderDate)
)on ps_testOrder(OrderDate)


 
create table testOrdersHistory
(
 	OrderID int identity(10000,1),
 	OrderDate datetime not null,
 	CustomerID int not null,
 	constraint PK_testOrdersHistory primary key(OrderID,OrderDate)
)on ps_testOrder(OrderDate)

 

INSERT INTO dbo.testOrder (OrderDate, CustomerID) VALUES ('2021-01-06 10:16:18.633', 1000);
INSERT INTO dbo.testOrder (OrderDate, CustomerID) VALUES ('2021-02-06 10:16:18.633', 1000);

-- 查看每个分区的数据
select $partition.pf_testOrder(OrderDate) as Patition,COUNT(*) countRow from dbo.testOrder 
group by $partition.pf_testOrder(OrderDate)

drop table dbo.testOrder 
drop table  dbo.testOrdersHistory 
select * from dbo.testOrder 
select * from dbo.testOrdersHistory