34,575
社区成员
发帖
与我相关
我的任务
分享
代码加注释,希望对初学者有用。
USE [master]
GO
if exists (select * from sys.databases where name = 'Test_1')
drop database Test_1
GO
--创建新库,要演练分区所以我们会多创建两个文件组Test_A,Test_B,以便在后面的分区方案中使用。
CREATE DATABASE [Test_1] ON PRIMARY
( NAME = N'test_1', FILENAME = N'D:\sqldata\test_1.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [test_A]
( NAME = N'Test_A', FILENAME = N'D:\sqldata\test_A.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [test_B]
( NAME = N'Test_B', FILENAME = N'D:\sqldata\test_B.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'D:\sqldata\Test_log.ldf' , SIZE = 7616KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
USE [Test_1]
GO
--若分区函数存在则先drop掉
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'test_partition')
DROP PARTITION FUNCTION [test_partition]
GO
/**//*创建分区函数给后面的分区方案使用,分区函数很简单就是指定一个范围确定在某个值为什么的时候放在那个分区上*/
--新建一个简单的分区函数,该函数以1000为界分两个区
create partition function test_partition(int)
AS
RANGE LEFT FOR VALUES (1000)
go
/**//*看分区方案是否存在,若存在先drop掉*/
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'test_scheme')
DROP PARTITION SCHEME test_scheme
GO
--创建分区方案,分区方案需要指定一个分区函数,并指定在分区函数中分的区需要放在哪一个文件组上
create partition scheme test_scheme
AS
PARTITION [test_partition] TO (test_A,test_B)
GO
--创建分区表
if object_id('student','U') is not null
drop table student;
go
create table student
(
id int identity(1,1) not null,
name varchar(10) not null,
class int not null,
grade int
) on test_scheme(class) --在此处指定该表要使用的分区方案,并将指定分区依据列
go
--随便插入几条数据
insert into student values ('AQU',10,100); -- 这条数据在A分区上
insert into student values ('AQU_边界',1000,89); -- 这边数据也在A分区上是个边界,因为我们上面在函数中指定的是RANGE LEFT,所以1000在A分区上
insert into student values ('BQU',1001,90); -- 这一条肯定是在B分区上了。
go
--最后看看结果。$partition.分区函数(分区列)可以返回某一行所在的分区序号
select *,分区序号 = $partition.test_partition(class) from student
GO
SQL Server 2005 分区表——滑动窗口方案实践:采用滑动窗口机制,把分区表的分区依次移入到另一个分区表。值得一提的是,本文示例涉及了 SQL 分区表的方方面面:建立分区函数(partition function)、分区方案(partition scheme);分区表创建;分区函数拆分、合并;分区表分区切换(partition swtich);分区索引(partition index)等诸多内容。
建立分区表 Orders
drop table dbo.Orders
go
drop partition scheme PS_Orders
go
drop partition function PF_Orders_OrderDateRange
go
-- 创建分区函数
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1996-01-01',
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- 创建分区方案
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
all to ([primary])
go
-- 创建分区表
create table dbo.Orders
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_Orders(OrderDate)
go
-- 创建聚集分区索引
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
-- 为分区表设置主键
alter table dbo.Orders add constraint PK_Orders primary key (OrderID, CustomerID, OrderDate)
go
-- 导入数据到分区表
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
from dbo.Orders_From_SQL2000_Northwind --(注:数据来源于 SQL Server 2000 示例数据库)
go
建立分区表 OrdersArchive (用来归档 Orders 表中不再活跃的数据)
drop table OrdersArchive
go
drop partition scheme PS_OrdersArchive
go
drop partition function PF_OrdersArchive_OrderDateRange
go
-- 创建分区函数
create partition function PF_OrdersArchive_OrderDateRange(datetime)
as
range right for values (
'1996-01-01',
'1997-01-01'
)
go
-- 创建分区方案
create partition scheme PS_OrdersArchive
as
partition PF_OrdersArchive_OrderDateRange
all to ([primary])
go
-- 创建分区表
create table dbo.OrdersArchive
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_OrdersArchive(OrderDate)
go
-- 创建聚集分区索引
create clustered index IXC_OrdersArchive_OrderDate on dbo.OrdersArchive(OrderDate)
go
-- 为分区表设置主键
alter table dbo.OrdersArchive add constraint PK_OrdersArchive
primary key (OrderID, CustomerID, OrderDate)
go
查看分区表分区函数的分区范围
exec dbo.sp_partition_range 'dbo.Orders'
go
exec dbo.sp_partition_ragne 'dbo.OrdersArchive'
go
获取 dbo.sp_partition_range sp_partition_range代码
查看分区表每个分区的数据分布情况
-- 查看分区表 Orders 每个分区的数据分布情况
select partition = $partition.PF_Orders_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.Orders
group by $partition.PF_Orders_OrderDateRange(OrderDate)
order by partition
go
-- 查看分区表 OrdersArchive 每个分区的数据分布情况
select partition = $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.OrdersArchive
group by $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
order by partition
运用滑动窗口机制,把分区表 Orders 分区数据迁移入 OrdersArchive
窗口滑动的步骤: 1. 在 OrdersArchive 分区表增加一个空闲分区。 2. 移动 Orders 一个分区到相应的 OrdersArchive 分区。 3. 删除 Orders 中的空闲分区。
移动订单日期为 1996 年的分区数据:
-- 为 OrderArchive 分区表的新增分区,指定存放位置。
alter partition scheme PS_OrdersArchive next used [primary]
go
-- 在 OrderArchive 新增一个分区(用来存放 1997 年数据)
alter partition function PF_OrdersArchive_OrderDateRange()
split range('1998-01-01')
go
-- 移动 Orders 1996 年数据到 OrderArchive
alter table dbo.Orders switch partition 2 to dbo.OrdersArchive partition 2
go
-- 合并 Orders 空闲分区(1996 年数据)
alter partition function PF_Orders_OrderDateRange()
merge range('1996-01-01')
go
移动订单日期为 1997 年的分区数据:
-- 为 OrderArchive 分区表的新增分区,指定存放位置。
alter partition scheme PS_OrdersArchive next used [primary]
go
-- 在 OrderArchive 新增一个分区(用来存放 1998 年数据)
alter partition function PF_OrdersArchive_OrderDateRange()
split range('1999-01-01')
go
-- 移动 Orders 1996 年数据到 OrderArchive
alter table dbo.Orders switch partition 2 to dbo.OrdersArchive partition 3
go
-- 合并 Orders 空闲分区(1997 年数据)
alter partition function PF_Orders_OrderDateRange()
merge range('1997-01-01')
go
移动订单日期为 1998 年的分区数据:
-- 为 OrderArchive 分区表的新增分区,指定存放位置。
alter partition scheme PS_OrdersArchive next used [primary]
go
-- 在 OrderArchive 新增一个分区(用来存放 1999 年数据)
alter partition function PF_OrdersArchive_OrderDateRange()
split range('2000-01-01')
go
-- 移动 Orders 1996 年数据到 OrderArchive
alter table dbo.Orders switch partition 2 to dbo.OrdersArchive partition 4
go
-- 合并 Orders 空闲分区(1998 年数据)
alter partition function PF_Orders_OrderDateRange()
merge range('1998-01-01')
go
经过以上三次移动,我们已经把分区表 Orders 中的数据全部移动到 OrdersArchive 中了。同时我们注意到:在这个窗口滑动过程中,代码中只有三处是变化的(有规律): “split range('2000-01-01')”。 “merge range('1998-01-01')”。 “to dbo.OrdersArchive partition 4”。这就为程序化处理,提供了条件。