分区表应用例子

引用联机帮助的说明：

      通过对表或索引进行分区，快速有效地移动数据子集。
      如果需要定期向已分区表添加新数据以及从同一已分区表中删除旧数据，可以使用这些方案。此操作会涉及各种方案中的大量或少量数据。如果必须加载、清除或转换要添加的新数据，则在将新数据作为分区添加之前可以将其视为独立的实体。可以将旧数据存档或存入数据仓库。无论集合有多大，都能快速有效地进行传输，因为并不以物理方式移动数据，这与 INSERT INTO SELECT FROM 语句不同。只有关于存储位置的元数据会从一个分区变为另一个分区。
      
在我们实际中，可以应用于出粮系统，考勤系统，已处理订单系统等。插入当月数据的时候，需要把上月的数据放到历史的数据表中，使用表分区方法就很好的能实现。

Use test
go
If Object_id('Duty') Is Not null 
    Drop Table Duty
Go
If Object_id('DutyHistory') Is Not null 
    Drop Table DutyHistory
Go

If Exists(Select 1 From sys.partition_schemes Where name='MyPS1')
    Drop Partition scheme MyPS1
Go
If Exists(Select 1 From sys.partition_functions Where name='MyPF1')
    Drop Partition Function MyPF1
Go

--建立分区函数
Create Partition Function MyPF1(datetime)
As Range Left
For Values('2007/12/31')
Go
--建立分区方案

Create Partition Scheme MyPS1
As Partition MyPF1
To ([Primary],[Primary])
Go
/*
1，建立分区函数，分区方案是有先后顺序的。
2，分区函数提供的值的数目n，不能超过 999。所创建的分区数等于 n + 1
*/
Go
--建立测试表
Create Table Duty(
        id int Identity(1,1) Not Null,
        Name nvarchar(50),
        WorkDate datetime,
        WorkHours numeric(5,1),
        Constraint PK_Duty Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
Go
Create Table DutyHistory(
        id int Identity(1,1) Not Null,
        Name nvarchar(50),
        WorkDate datetime,
        WorkHours numeric(5,1),
        Constraint PK_DutyHistory Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)

--这里就是用到了表分区方案。
Go
--插入2008/1一个月的数据
Declare 
        @MaxRows int,
        @StartDate datetime

Select 
    @MaxRows=31,
    @StartDate='2007/12/31'
    
;With 
a0 as(Select id=1 Union All Select id=1),
a1 as(Select id=a.id From a0 a,a0 b),
a2 as(Select id=a.id From a1 a,a1 b),
a3 as(Select id=a.id From a2 a,a2 b),
a4 as(Select id=a.id From a3 a,a3 b),
a5 as(Select id=a.id From a4 a,a4 b),
Row as(Select id=Row_number() Over(Order By id) From a5)
Insert Into Duty 
    ([name],WorkDate,WorkHours)
Select 'Robert',@StartDate+id,Rand()*6 From Row Where id<=@MaxRows
/*
--debug 查询两表的分区情况
Select * from sys.partitions 
Where object_id In(Select object_id From sys.tables Where name In('Duty','DutyHistory'))
*/
Go
--切换分区前
Select * From Duty
Select * From DutyHistory
--切换分区 Duty -->DutyHistory 
Alter Table Duty Switch Partition 2 To DutyHistory Partition 2
--切换分区后
Select * From Duty
Select * From DutyHistory

Go
--修改分区函数,分区方案，方便下个月切换分区

--1.把>2007/12/31的分区拆分为两个分区
Alter Partition Scheme MyPS1 
Next Used [Primary] 
Go
Alter Partition Function MyPF1()
Split Range('2008/1/31') 
Go
--2.把<2008/1/31的两个分区合并
Alter Partition Function MyPF1()
Merge Range('2007/12/31') 
Go
--插入2月份数据
Declare 
        @MaxRows int,
        @StartDate datetime

Select 
    @MaxRows=29,
    @StartDate='2008/1/31'
    
;With 
a0 as(Select id=1 Union All Select id=1),
a1 as(Select id=a.id From a0 a,a0 b),
a2 as(Select id=a.id From a1 a,a1 b),
a3 as(Select id=a.id From a2 a,a2 b),
a4 as(Select id=a.id From a3 a,a3 b),
a5 as(Select id=a.id From a4 a,a4 b),
Row as(Select id=Row_number() Over(Order By id) From a5)
Insert Into Duty 
    ([name],WorkDate,WorkHours)
Select 'Robert',@StartDate+id,Rand()*6 From Row Where id<=@MaxRows

Go
--切换分区前
Select * From Duty
Select * From DutyHistory
--切换分区 Duty -->DutyHistory 
Alter Table Duty Switch Partition 2 To DutyHistory Partition 2
--切换分区后
Select * From Duty
Select * From DutyHistory