开篇介绍
基于上一篇文章 SQL SERVER 表分区笔记 - 案例分享 继续了解 Sliding Window 滑动窗口的处理
Note: 示例中使用到了SQL Server 2000的Demo Database, 可以从此链接中下载
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654
案例分享
/****************************************************************
-- Order 分区表
******************************************************************/
IF OBJECT_ID('dbo.Orders')IS NOT NULL
DROP TABLE Orders
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_Orders')
DROP PARTITION SCHEME PS_Orders
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_Orders_OrderDateRange')
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 Northwind.dbo.Orders
GO
/****************************************************************
*** Order Archive 归档分区表,归档那些不经常查询不活跃的历史数据
*** 本示例中需要归档的数据1996年和年
******************************************************************/
IF OBJECT_ID('dbo.OrdersArchive')IS NOT NULL
DROP TABLE OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_OrdersArchive')
DROP PARTITION SCHEME PS_OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_OrdersArchive_OrderDateRange')
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
/************************************************************************
查看分区表分区函数的分区范围
Orders -
PF_Orders_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
OrdersArchive
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < NULL
************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.Orders'
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
GO
/***************************************************************************
查询源分区表结果1996年以前的数据没有,所以分区是空的
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
3 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
4 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
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
/***************************************************************************
查询存档表结果 没有数据
***************************************************************************/
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
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Split之前OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
-- 在OrderArchive 新增一个分区(用来存放1997 年数据)
ALTER PARTITION FUNCTION PF_OrdersArchive_OrderDateRange()
SPLIT RANGE('1998-01-01')
GO
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Split之后OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
-- 移动Orders 1996 年数据到OrderArchive
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 2
GO
/***************************************************************************
查询源分区表结果1996年位于分区 已经被Switch 到OrderArchive的分区了
所以不会再有分区的数据
3 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
4 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
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
/***************************************************************************
查询存档表结果 2分区存储了年的数据是从Orders 表的分区Switch 过来的
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
***************************************************************************/
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
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
在Switch后OrdersArchive 的分区情况
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/***************************************************************************
在Switch后Orders 的分区情况
PF_Orders_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
/****************************************************************************
-- 合并Orders 空闲分区(1996 年数据)
*****************************************************************************/
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
MERGE RANGE('1996-01-01')
GO
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/****************************************************************************
-- 合并Orders 空闲分区(1996 年数据)后, Orders 分区情况
-- 之前分区相当于被合并到分区中成为新的分区
PF_Orders_OrderDateRange 1 NULL <= val < 1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1999-01-01 00:00:00.000 <= val < NULL
*****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
-- 合并Orders 空闲分区(1996 年数据)后, OrdersArchive 分区情况没有影响
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
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
/***************************************************************************
查询上面源分区表结果现在位于分区的是年的数据,因为之前的分区已经给
合并了,所以分区的边界变成了 NULL < Val <= 1997
2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
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
/***************************************************************************
查询存档表结果
2 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
***************************************************************************/
/*************************************************************************
运用滑动窗口机制,把分区表Orders 分区数据迁移入OrdersArchive
窗口滑动的步骤:
1. 在OrdersArchive 分区表增加一个空闲分区
2. 移动Orders 一个分区到相应的OrdersArchive 分区
3. 删除Orders 中的空闲分区
练习移动订单日期为1997 年的分区数据
1. 在为OrdersArchive 分区表增加一个空闲分区的步骤
a. 为OrderArchive 分区表的新增分区指定存放位置
b. 利用Split来分隔分区即新增了分区
**************************************************************************/
-- 步骤一:增加新的空闲分区
-- a. 指定新分区的存放位置,实则修改分区方案
ALTER PARTITION SCHEME PS_OrdersArchive NEXT USED [PRIMARY]
GO
-- b. 新增分区,现在最大的已知的分区键列值是
ALTER PARTITION FUNCTION PF_OrdersArchive_OrderDateRange()
SPLIT RANGE('1999-01-01')
-- 步骤二:移动Orders 一个分区到相应的OrdersArchive 分区
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 3
-- 步骤三：删除Orders 中的空闲分区
ALTER PARTITION FUNCTION PF_Orders_OrderDateRange()
MERGE RANGE('1997-01-01')
EXEC dbo.sp_show_partition_range 'dbo.Orders'
/****************************************************************************
PF_Orders_OrderDateRange 1 NULL <= val < 1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1999-01-01 00:00:00.000 <= val < NULL
*****************************************************************************/
EXEC dbo.sp_show_partition_range 'dbo.OrdersArchive'
/***************************************************************************
PF_OrdersArchive_OrderDateRange 1 NULL <= val < 1996-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 2 1996-01-01 00:00:00.000 <= val < 1997-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 3 1997-01-01 00:00:00.000 <= val < 1998-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 4 1998-01-01 00:00:00.000 <= val < 1999-01-01 00:00:00.000
PF_OrdersArchive_OrderDateRange 5 1999-01-01 00:00:00.000 <= val < NULL
****************************************************************************/
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
/***************************************************************************
2 270 1998-01-01 00:00:00.000 &
