--=========================================
-- 转换为分区表
--=========================================
-- 1. 创建分区函数
-- a. 适用于存储历史存档记录的分区表的分区函数
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Year, 1, @dt))
-- b. 适用于存储历史记录的分区表的分区函数
--DECLARE @dt datetime
SET @dt = '20000101'
CREATE PARTITION FUNCTION PF_History(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Month, 1, @dt),
DATEADD(Month, 2, @dt),
DATEADD(Month, 3, @dt),
DATEADD(Month, 4, @dt),
DATEADD(Month, 5, @dt),
DATEADD(Month, 6, @dt),
DATEADD(Month, 7, @dt),
DATEADD(Month, 8, @dt),
DATEADD(Month, 9, @dt),
DATEADD(Month, 10, @dt),
DATEADD(Month, 11, @dt),
DATEADD(Month, 12, @dt))
GO
-- 2. 创建分区架构
-- a. 适用于存储历史存档记录的分区表的分区架构
CREATE PARTITION SCHEME PS_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])
-- b. 适用于存储历史记录的分区表的分区架构
CREATE PARTITION SCHEME PS_History
AS PARTITION PF_History
TO([PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY])
GO
-- 3. 删除索引
-- a. 删除存储历史存档记录的表中的索引
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
-- b. 删除存储历史记录的表中的索引
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO
-- 4. 转换为分区表
-- a. 将存储历史存档记录的表转换为分区表
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH(
MOVE TO PS_HistoryArchive(TransactionDate))
-- b.将存储历史记录的表转换为分区表
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH(
MOVE TO PS_History(TransactionDate))
GO
-- 5. 恢复主键
-- a. 恢复存储历史存档记录的分区表的主键
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
-- b. 恢复存储历史记录的分区表的主键
ALTER TABLE Production.TransactionHistory
ADD CONSTRAINT PK_TransactionHistory_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
GO
-- 6. 恢复索引
-- a. 恢复存储历史存档记录的分区表的索引
CREATE INDEX IX_TransactionHistoryArchive_ProductID
ON Production.TransactionHistoryArchive(
ProductID)
CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)
-- b. 恢复存储历史记录的分区表的索引
CREATE INDEX IX_TransactionHistory_ProductID
ON Production.TransactionHistory(
ProductID)
CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO
-- 7. 查看分区表的相关信息
SELECT
SchemaName = S.name,
TableName = TB.name,
PartitionScheme = PS.name,
PartitionFunction = PF.name,
PartitionFunctionRangeType = CASE
WHEN boundary_value_on_right = 0 THEN 'LEFT'
ELSE 'RIGHT' END,
PartitionFunctionFanout = PF.fanout,
SchemaID = S.schema_id,
ObjectID = TB.object_id,
PartitionSchemeID = PS.data_space_id,
PartitionFunctionID = PS.function_id
FROM sys.schemas S
INNER JOIN sys.tables TB
ON S.schema_id = TB.schema_id
INNER JOIN sys.indexes IDX
on TB.object_id = IDX.object_id
AND IDX.index_id < 2
INNER JOIN sys.partition_schemes PS
ON PS.data_space_id = IDX.data_space_id
INNER JOIN sys.partition_functions PF
ON PS.function_id = PF.function_id
GO
--=========================================
-- 移动分区表数据
--=========================================
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
-- a. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
-- b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
-- c. 将历史记录表中的过期数据移动到历史存档记录表中
ALTER TABLE Production.TransactionHistory
SWITCH PARTITION 2
TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)
-- d. 将接受到的数据与原来的分区合并
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
GO
-- 2. 将存储历史记录的分区表中不包含数据的分区删除, 并增加新的分区以接受新数据
-- a. 合并不包含数据的分区
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)
-- b. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_History
NEXT USED [PRIMARY]
-- c. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20041001'
ALTER PARTITION FUNCTION PF_History()
SPLIT RANGE(@dt)
GO
--=========================================
-- 清除历史存档记录中的过期数据
--=========================================
-- 1. 创建用于保存过期的历史存档数据的表
CREATE TABLE Production.TransactionHistoryArchive_2001_temp(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL
DEFAULT ((0)),
TransactionDate datetime NOT NULL
DEFAULT (GETDATE()),
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL
DEFAULT (GETDATE()),
CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
)
-- 2. 将数据从历史存档记录分区表移动到第步创建的表中
ALTER TABLE Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO Production.TransactionHistoryArchive_2001_temp
-- 3. 删除不再包含数据的分区
DECLARE @dt datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
-- 4. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
-- 5. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
分享到:
相关推荐
详细介绍了有关SQL Server2005数据库分区表的技术要点和相关知识,精彩!不可多得!
SQL Server 2005分区表技术的研究与实现.pdf
在SQL Server 2005开发平台下开发某超市业务分析系统中,采用实现关键业务表的分区技术来分解大型表。 结果表明,表的分区技术能明显改善系统的响应速度和可维护性。
MS SQL Server:分区表、分区索引 详解 1. 分区表简介 使用分区表的主要目的,是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。 大型表:数据量巨大的表。 访问模式:因目的不同,需访问的...
SQL Server 2005 中的分区表和索引
sqlserver2005表分区方案建设
查看sqlserver表分区数据分布,需要的同学下载看看吧
SQL Server 在处理大型数据中,如果我们把安系统默认的格式,将所有数据都写到一个文件中,那么我们对数据的操作可想而知,效率决对是相当低的,为了让SQL Server也能处理大型数据,SQL Server 2005开始支持分区表,...
关于sqlserver数据库分区表建立的代码、分区文件、分区文件组,非常实用,供参考下载,本代码仅供那些爱好数据库学习的使用者共享。
sqlServer数据库大表分区方案,新建表的时候。。如何表分区。
SQLServer2005中的分区表技术精华 分区表技术,创建表分区方法与步骤,实际创建分区的例子。
建立分区表需要创建文件组,而创建多个文件组的目的是为了获得好的 I/O 平衡。 在 SQL Server 2005 中引入的表分区技术,允许用户将数据分散存放到不同的物理磁盘中,以提高查询性能。表分区操作过程由三个步骤组成...
您想提高SQL Server 数据库性能吗?您想研究一下表分区吗?这里有最详细的文档说明和示例!还等什么赶快下载吧!
SQL SERVER 2008分区表快速创建.doc
SQLserver创建分区表.pdf
详细的说明了sql server2008数据库中分区表的工作原理,并详细的介绍了使用方法,特此上传,必备留用。
分区索引 分区索引 分区索引 分区索引分区索引分区索引分区索引分区索引
SQL Server 2000 分区方案
超大型数据库的大小常常达到数百GB,有时甚至要用TB来计算。而单表的数据量往往会达到上亿的记录,并且记录数会随着时间而增长。...本文通过一个具体实例,介绍如何创建和修改分区表,以及如何查看分区表。