/***************************************************************
BIWORK 分区表阅读与实践笔记
Note: 示例中使用到了SQL Server 2000的 Demo Database, 可以从此链接中下载
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654
在检查删除Partition Function 以及Partition Scheme 时,要注意
Partition Scheme 引用了Partition Function, 所有需要先删除
Partition Scheme. 同理,引用了Partition Scheme 的表应该先删除掉.
引用关系: TABLE -> PARTITION SCHEME -> PARTITION FUNCTION
***************************************************************/
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
/***************************************************************
1. 如果在不需要对数据库进行物理分组的情况下,比如分区表还是享用同
一个文件组,那么应该可以从创建分区函数开始
*** 创建分区函数
*** 确定分区键列的类型(DATETIME)以及分区的边界值:
(''1997-01-01','1998-01-01','1999-01-01'')
*** N个边界值确定N+1 个分区
*** RIGHT - 第一个分区的所有值都小于VAL < 1997-01-1
第二个分区的值范围是1997-01-01 <= VAL < 1998-01-01
***************************************************************/
CREATE PARTITION FUNCTION PF_Orders_OrderDateRange(DATETIME)
AS
RANGE RIGHT FOR VALUES
(
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
GO
EXEC dbo.sp_show_partition_range
@partition_function = 'PF_Orders_OrderDateRange'
/***************************************************************
显示分区函数的分区情况,PARTITION FUNCTION,PARTITION,MinVal,VALUE,MaxVal
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
****************************************************************/
/***************************************************************
2. 创建了分区函数后,便可以创建分区方案
*** 因为在上一个分区函数中有个边界值,4个分区,并且并没有其它的
数据库文件组,所以当分区方案应用到具体的分区函数时所有的分区
都是指向PRIMARY 文件组
***************************************************************/
CREATE PARTITION SCHEME PS_Orders
AS
PARTITION PF_Orders_OrderDateRange
TO ([primary],[primary],[primary],[primary])
GO
/***************************************************************
3. 创建分区表时要应用分区方案,并提供具体的分区键列
ON 分区函数(分区键列)
****************************************************************/
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
/******************************************************************
4. 在创建分区表后,需要创建聚集分区索引
*** 根据订单表Orders 查询时经常使用OrderDate 范围条件来查询的特点,
*** 我们最好在Orders.OrderDate 列上建立聚集索引（clustered index）.
*** 为了便于进行分区切换（partition swtich)
大多数情况下,建议在分区表上建立分区索引。
*******************************************************************/
CREATE CLUSTERED INDEX IXC_Orders_OrderDate ON dbo.Orders(OrderDate)
GO
/*******************************************************************
5. 为分区表创建主键
如果主键不包含分区键列,将会出现以下错误信息:
Msg 1908, Level 16, State 1, Line 2
Column 'OrderDate' is partitioning column of the index 'PK_Orders'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
原因:
主键实际上是个唯一索引,但分区表在建立唯一索引（分区索引）的时候,
分区列必须是唯一索引的一部分.因为SQL Server 不但要保证索引在各个
分区是唯一的,还要保证在整个表中是唯一的.
********************************************************************/
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
/************************************************************************
查看分区表Orders 上的索引:
IXC_Orders_OrderDate|clustered located on PS_Orders|OrderDate
PK_Orders|nonclustered,unique,primary key located on PS_Orders|OrderID, CustomerID,
OrderDate
************************************************************************/
EXEC sp_helpindex 'dbo.Orders'
/**********************************************************************
6. 从SQL Server 2000 NorthWind 导入测试数据
***********************************************************************/
INSERT INTO dbo.Orders
SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate
FROM Northwind.dbo.Orders
/************************************************************************
7. 查看分区表各分区数据情况（数据行数，最大最小OrderDate 值）
*************************************************************************/
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
/************************************************************************
在目前的测试数据中,并没有大于1999年的数据,所以在上面的查询结果中并没有
看到第个分区的信息: PARTITION,ROWS,MinVal,MaxVal
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
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
*************************************************************************/
-- 插入一条测试数据
INSERT INTO dbo.Orders VALUES(11111,'TEST',1,'2000-10-10 10:10:10:100')
-- 再次查询
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
/************************************************************************
查询结果显示了个分区的信息
PARTITION,ROWS,MinVal,MaxVal
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
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
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
*************************************************************************/
GO
/*************************************************************************
************* 切换分区表的一个分区到普通数据表***************************
************* Partition to Table ****************************************/
/*************************************************************************
1. 首先建立普通数据表Orders_1998，该表用来存放订单日期为1998 年的所有数据
2. 分区到普通表的切换，最好满足以下的前提条件:
a. 普通表必须建立在分区表切换分区所在的文件组上ON [PRIMARY]
b. 普通表的表结构跟分区表的一致
c. 普通表上的索引要跟分区表一致(聚集索引,非聚集索引)
d. 普通表必须是空表，不能有任何数据
*************************************************************************/
IF OBJECT_ID('Orders_1998') IS NOT NULL
DROP TABLE Orders_1998
GO
CREATE TABLE dbo.Orders_1998
(
OrderID INT NOT NULL,
CustomerID VARCHAR(10) NOT NULL,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL
)ON [PRIMARY]
GO
-- 添加聚集索引,和分区表一致
CREATE CLUSTERED INDEX IXC_Orders1998_OrderDate ON dbo.Orders_1998(OrderDate)
GO
-- 添加主键,和分区表一致
ALTER TABLE dbo.Orders_1998 ADD CONSTRAINT PK_Orders_1998
PRIMARY KEY(OrderID,CustomerID,OrderDate)
GO
/***************************************************************************
** 开始切换分区表Orders 第三个分区的数据(1998年的数据)到普通表Orders_1998
** 关键字- SWITCH PARTITION [NUMBER] TO [History Table]
***************************************************************************/
ALTER TABLE dbo.Orders SWITCH PARTITION 3 TO dbo.Orders_1998
/***************************************************************************
查询源分区表结果
分区号为的数据已经没有了
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查询存档表结果
3 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_1998
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/************************************************************************
************* 切换普通表数据到分区表的一个分区中 *********************
************* Table to Partition ************************/
/*************************************************************************
上面我们已经把分区表Orders 第三个分区的数据切换到普通表Orders_1998 中了,
现在我们再切换回来:
**************************************************************************/
ALTER TABLE dbo.Orders_1998 SWITCH PARTITION 3 TO dbo.Orders
/*************************************************************************
错误信息:
Msg 4911, Level 16, State 2, Line 1
Cannot specify a partitioned table without partition number in ALTER TABLE
SWITCH statement. The table 'SSISDemoDB.dbo.Orders' is partitioned.
原因:
实际上应该是将dbo.Orders_1998 表中的数据SWITCH 到dbo.Orders 表的
Partition 分区中.
而不能说是将dbo.Orders_1998 的分区的数据SWITCH 到dbo.Orders 全表中
**************************************************************************/
ALTER TABLE dbo.Orders_1998 SWITCH TO dbo.Orders PARTITION 3
/*************************************************************************
错误信息:
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table
'dbo.Orders_1998' allow values that are not allowed by range defined by
partition 3 on target table 'dbo.Orders'.
原因:
表dbo.Orders 的数据经过分区函数的分区列定义, 各个分区的数据实际上已经经过
了数据约束检查,符合分区边界范围(Range)的数据才会录入到各个分区中.
但是在历史表/存档表dbo.Orders_1998中的数据实际上是没有边界约束的,比如完全
可以手动的插入一条年的数据,这样一来在进行SWITCH时肯定是不会成功的.
所以在SWITCH时,先进行了约束性检查,尽管没有不符合规范的数据,但是有潜在的威胁.
所以在SWITCH之前,先为dbo.Orders_1998添加一个检查约束,并再次SWITCH,成功！
**************************************************************************/
ALTER TABLE dbo.Orders_1998 ADD CONSTRAINT CK_Orders1998_OrderDate
CHECK(OrderDate>='1998-01-01' AND OrderDate<'1999-01-01')
ALTER TABLE dbo.Orders_1998 SWITCH TO dbo.Orders PARTITION 3
/***************************************************************************
查询源分区表结果,分区的数据已经从dbo.Orders_1998 回来了
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
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
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查询存档表结果,没有任何数据,已经成功SWITCH to Orders 表的PARTITION 3
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders_1998
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/****************************************************************************
所以在进行存档表的数据向分区表迁移过程中(TABLE TO PARTITION),
相比(PARTITION TO TABLE)多一个条件:
普通表必须加上和分区数据范围一致的约束条件.
*****************************************************************************/
/************************************************************************
************* 切换分区表数据到分区表 ********************************
************* PARTITION TO PARTITION **********************************/
/*************************************************************************
-- 新的存档分区表在结构上和源分区表是一致的,包括分区函数和分区方案,
但是需要重新创建,不能简单地直接使用dbo.Orders 表上的分区函数和分区方案,
因为他们之间有绑定关系.
**************************************************************************/
IF OBJECT_ID('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
(
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
GO
CREATE PARTITION SCHEME PS_OrdersArchive
AS
-- 分区Scheme和分区函数绑定了
PARTITION PF_OrdersArchive_OrderDateRange
TO ([primary],[primary],[primary],[primary])
GO
CREATE TABLE dbo.OrdersArchive
(
OrderID INT NOT NULL,
CustomerID VARCHAR(10) NOT NULL,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL
)
-- 表和分区Scheme绑定了
ON PS_OrdersArchive(OrderDate)
GO
CREATE CLUSTERED INDEX IXC_OrdersArchive_OrderDate ON dbo.OrdersArchive(OrderDate)
ALTER TABLE dbo.OrdersArchive ADD CONSTRAINT PK_OrdersArchive
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
/*********************************************************************************
开始切换分区
**********************************************************************************/
ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersArchive PARTITION 1
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersArchive PARTITION 2
ALTER TABLE dbo.Orders SWITCH PARTITION 3 TO dbo.OrdersArchive PARTITION 3
/***************************************************************************
查询源分区表结果,只会有分区的数据
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查询存档表结果,已经成功转移
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
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
/***************************************************************************
总结: 分区表分区切换并没有真正去移动数据,而是SQL Server 在系统底层改变了表
的元数据。因此分区表分区切换是高效,快速,灵活的.利用分区表的分区切换功能,
我们可以快速加载数据到分区表.卸载分区数据到普通表,然后TRUNCATE 普通表,
以实现快速删除分区表数据,快速归档不活跃数据到历史表。
****************************************************************************/
注 : 已经不记得原博客地址了, 这篇日志是基于别人的分析成果之上加上自己亲自实践, 思考, 重 新添加了一些代码和注释. 在注解和结论验证方面按照自己的理解做出了还算比较细致的说明,对刚接触 表分区概念的朋友们相信会有很大的帮助. 我也是通过这种方式学习和掌握了表分区的一些基础操作步 骤和处理方式.
以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索scheme
， 函数
， 分区表
， partition
边界
biwork、实践笔记、冬季旅游的实践笔记、小学生实践笔记、实践笔记400字，以便于您获取更多的相关知识。
时间： 2024-09-18 18:23:37