SQL大表转为分区表实例
--
进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = ' AdventureWorks.bak '
WITH FORMAT
-- -- 恢复
-- RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO
-- =========================================
-- 转换为分区表
-- =========================================
USE AdventureWorks
GO
-- 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 = ' 20030901 '
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. 将数据从历史存档记录分区表移动到第1步创建的表中
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 )
查询分区信息:
; WITH
TBINFO AS (
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
),
PF1 AS (
SELECT PFP.function_id, PFR.boundary_id, PFR.value, Type = CONVERT (sysname,
CASE T.name
WHEN ' numeric ' THEN ' decimal '
WHEN ' real ' THEN ' float '
ELSE T.name END
+ CASE
WHEN T.name IN ( ' decimal ' , ' numeric ' )
THEN QUOTENAME ( RTRIM (PFP. precision )
+ CASE WHEN PFP.scale > 0 THEN ' , ' + RTRIM (PFP.scale) ELSE '' END , ' () ' )
WHEN T.name IN ( ' float ' , ' real ' )
THEN QUOTENAME (PFP. precision , ' () ' )
WHEN T.name LIKE ' n%char '
THEN QUOTENAME (PFP.max_length / 2 , ' () ' )
WHEN T.name LIKE ' %char ' OR T.name LIKE ' %binary '
THEN QUOTENAME (PFP.max_length, ' () ' )
ELSE '' END )
FROM sys.partition_parameters PFP
LEFT JOIN sys.partition_range_values PFR
ON PFR.function_id = PFP.function_id
AND PFR.parameter_id = PFP.parameter_id
INNER JOIN sys.types T
ON PFP.system_type_id = T.system_type_id
),
PF2 AS (
SELECT * FROM PF1
UNION ALL
SELECT
function_id, boundary_id = boundary_id - 1 , value, type
FROM PF1
WHERE boundary_id = 1
),
PF AS (
SELECT
B.function_id, boundary_id = ISNULL (B.boundary_id + 1 , 1 ),
value = STUFF (
CASE
WHEN A.boundary_id IS NULL THEN ''
ELSE ' AND [partition_column_name] ' + PF.LessThan + ' ' + CONVERT ( varchar ( max ), A.value) END
+ CASE
WHEN A.boundary_id = 1 THEN ''
ELSE ' AND [partition_column_name] ' + PF.MoreThan + ' ' + CONVERT ( varchar ( max ), B.value) END ,
1 , 5 , '' ),
B.Type
FROM PF1 A
RIGHT JOIN PF2 B
ON A.function_id = B.function_id
AND (A.boundary_id - 1 = B.boundary_id
OR (A.boundary_id IS NULL AND B.boundary_id IS NULL ))
INNER JOIN (
SELECT
function_id,
LessThan = CASE
WHEN boundary_value_on_right = 0 THEN ' <= '
ELSE ' < ' END ,
MoreThan = CASE
WHEN boundary_value_on_right = 0 THEN ' > '
ELSE ' >= ' END
FROM sys.partition_functions
)PF
ON B.function_id = PF.function_id
),
PS AS (
SELECT
DDS.partition_scheme_id, DDS.destination_id,
FileGroupName = FG.name, IsReadOnly = FG.is_read_only
FROM sys.destination_data_spaces DDS
INNER JOIN sys.filegroups FG
ON DDS.data_space_id = FG.data_space_id
),
PINFO AS (
SELECT
RowID = ROW_NUMBER() OVER ( ORDER BY SchemaID, ObjectID, PS.destination_id),
TB.SchemaName, TB.TableName,
TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly,
TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout,
PF.boundary_id, PF.Type, PF.value
FROM TBINFO TB
INNER JOIN PS
ON TB.PartitionSchemeID = PS.partition_scheme_id
LEFT JOIN PF
ON TB.PartitionFunctionID = PF.function_id
AND PS.destination_id = PF.boundary_id
)
SELECT
RowID,
SchemaName = CASE destination_id
WHEN 1 THEN SchemaName
ELSE N '' END ,
TableName = CASE destination_id
WHEN 1 THEN TableName
ELSE N '' END ,
PartitionScheme = CASE destination_id
WHEN 1 THEN PartitionScheme
ELSE N '' END ,
destination_id, FileGroupName, IsReadOnly,
PartitionFunction = CASE destination_id
WHEN 1 THEN PartitionFunction
ELSE N '' END ,
PartitionFunctionRangeType = CASE destination_id
WHEN 1 THEN PartitionFunctionRangeType
ELSE N '' END ,
PartitionFunctionFanout = CASE destination_id
WHEN 1 THEN CONVERT ( varchar ( 20 ), PartitionFunctionFanout)
ELSE N '' END ,
boundary_id = ISNULL ( CONVERT ( varchar ( 20 ), boundary_id), '' ),
Type = ISNULL (Type, N '' ),
value = CASE PartitionFunctionFanout
WHEN 1 THEN ' <ALL Data> '
ELSE ISNULL (value, N ' <NEXT USED> ' ) END
FROM PINFO
ORDER BY RowID
-- ==================================
-- drop database dbPartitionTest
-- 测试数据库
create database dbPartitionTest
go
use
dbPartitionTest
go
-- 增加分组
alter database dbPartitionTest ADD FILEGROUP P200801
alter database dbPartitionTest ADD FILEGROUP P200802
alter database dbPartitionTest ADD FILEGROUP P200803
go
-- 分区函数
CREATE PARTITION FUNCTION part_Year( datetime )
AS RANGE LEFT FOR VALUES
(
’ 20080131 23 : 59 : 59.997 ’,’ 20080229 23 : 59 : 59.997 ’,’ 20080331 23 : 59 : 59.997 ’
)
go
-- 增加文件组
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200801’,FILENAME = N’c:tb_P200801.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200801
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200802’,FILENAME = N’c:tb_P200802.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200802
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200803’,FILENAME = N’c:tb_P200803.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200803
go
-- 分区架构
CREATE PARTITION SCHEME part_YearScheme AS PARTITION part_Year TO (P200801,P200802,P200803, [ PRIMARY ] )
go
CREATE TABLE [ dbo ] .t_part
(name varchar ( 100 ) default newid (),date datetime NOT NULL )
ON part_YearScheme (date)
go
-- 添加测试数据,每天1条
declare @date datetime
set @date = ’ 2007 - 12 - 31 ’
while @date <= ’ 2008 - 04 - 0 ’
1 begin
insert into t_part(date) values ( @date )
set @date = @date + 1
end
go
-- 查询数据分布在哪些分区
select $partition.part_Year(date) as 分区编号, * from t_part order by date
-- 查询数据库文件
go
sp_helpfile
USE master
-- 备份
BACKUP DATABASE AdventureWorks
TO DISK = ' AdventureWorks.bak '
WITH FORMAT
-- -- 恢复
-- RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO
-- =========================================
-- 转换为分区表
-- =========================================
USE AdventureWorks
GO
-- 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 = ' 20030901 '
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. 将数据从历史存档记录分区表移动到第1步创建的表中
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 )
查询分区信息:
; WITH
TBINFO AS (
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
),
PF1 AS (
SELECT PFP.function_id, PFR.boundary_id, PFR.value, Type = CONVERT (sysname,
CASE T.name
WHEN ' numeric ' THEN ' decimal '
WHEN ' real ' THEN ' float '
ELSE T.name END
+ CASE
WHEN T.name IN ( ' decimal ' , ' numeric ' )
THEN QUOTENAME ( RTRIM (PFP. precision )
+ CASE WHEN PFP.scale > 0 THEN ' , ' + RTRIM (PFP.scale) ELSE '' END , ' () ' )
WHEN T.name IN ( ' float ' , ' real ' )
THEN QUOTENAME (PFP. precision , ' () ' )
WHEN T.name LIKE ' n%char '
THEN QUOTENAME (PFP.max_length / 2 , ' () ' )
WHEN T.name LIKE ' %char ' OR T.name LIKE ' %binary '
THEN QUOTENAME (PFP.max_length, ' () ' )
ELSE '' END )
FROM sys.partition_parameters PFP
LEFT JOIN sys.partition_range_values PFR
ON PFR.function_id = PFP.function_id
AND PFR.parameter_id = PFP.parameter_id
INNER JOIN sys.types T
ON PFP.system_type_id = T.system_type_id
),
PF2 AS (
SELECT * FROM PF1
UNION ALL
SELECT
function_id, boundary_id = boundary_id - 1 , value, type
FROM PF1
WHERE boundary_id = 1
),
PF AS (
SELECT
B.function_id, boundary_id = ISNULL (B.boundary_id + 1 , 1 ),
value = STUFF (
CASE
WHEN A.boundary_id IS NULL THEN ''
ELSE ' AND [partition_column_name] ' + PF.LessThan + ' ' + CONVERT ( varchar ( max ), A.value) END
+ CASE
WHEN A.boundary_id = 1 THEN ''
ELSE ' AND [partition_column_name] ' + PF.MoreThan + ' ' + CONVERT ( varchar ( max ), B.value) END ,
1 , 5 , '' ),
B.Type
FROM PF1 A
RIGHT JOIN PF2 B
ON A.function_id = B.function_id
AND (A.boundary_id - 1 = B.boundary_id
OR (A.boundary_id IS NULL AND B.boundary_id IS NULL ))
INNER JOIN (
SELECT
function_id,
LessThan = CASE
WHEN boundary_value_on_right = 0 THEN ' <= '
ELSE ' < ' END ,
MoreThan = CASE
WHEN boundary_value_on_right = 0 THEN ' > '
ELSE ' >= ' END
FROM sys.partition_functions
)PF
ON B.function_id = PF.function_id
),
PS AS (
SELECT
DDS.partition_scheme_id, DDS.destination_id,
FileGroupName = FG.name, IsReadOnly = FG.is_read_only
FROM sys.destination_data_spaces DDS
INNER JOIN sys.filegroups FG
ON DDS.data_space_id = FG.data_space_id
),
PINFO AS (
SELECT
RowID = ROW_NUMBER() OVER ( ORDER BY SchemaID, ObjectID, PS.destination_id),
TB.SchemaName, TB.TableName,
TB.PartitionScheme, PS.destination_id, PS.FileGroupName, PS.IsReadOnly,
TB.PartitionFunction, TB.PartitionFunctionRangeType, TB.PartitionFunctionFanout,
PF.boundary_id, PF.Type, PF.value
FROM TBINFO TB
INNER JOIN PS
ON TB.PartitionSchemeID = PS.partition_scheme_id
LEFT JOIN PF
ON TB.PartitionFunctionID = PF.function_id
AND PS.destination_id = PF.boundary_id
)
SELECT
RowID,
SchemaName = CASE destination_id
WHEN 1 THEN SchemaName
ELSE N '' END ,
TableName = CASE destination_id
WHEN 1 THEN TableName
ELSE N '' END ,
PartitionScheme = CASE destination_id
WHEN 1 THEN PartitionScheme
ELSE N '' END ,
destination_id, FileGroupName, IsReadOnly,
PartitionFunction = CASE destination_id
WHEN 1 THEN PartitionFunction
ELSE N '' END ,
PartitionFunctionRangeType = CASE destination_id
WHEN 1 THEN PartitionFunctionRangeType
ELSE N '' END ,
PartitionFunctionFanout = CASE destination_id
WHEN 1 THEN CONVERT ( varchar ( 20 ), PartitionFunctionFanout)
ELSE N '' END ,
boundary_id = ISNULL ( CONVERT ( varchar ( 20 ), boundary_id), '' ),
Type = ISNULL (Type, N '' ),
value = CASE PartitionFunctionFanout
WHEN 1 THEN ' <ALL Data> '
ELSE ISNULL (value, N ' <NEXT USED> ' ) END
FROM PINFO
ORDER BY RowID
-- ==================================
-- drop database dbPartitionTest
-- 测试数据库
create database dbPartitionTest
go
use
dbPartitionTest
go
-- 增加分组
alter database dbPartitionTest ADD FILEGROUP P200801
alter database dbPartitionTest ADD FILEGROUP P200802
alter database dbPartitionTest ADD FILEGROUP P200803
go
-- 分区函数
CREATE PARTITION FUNCTION part_Year( datetime )
AS RANGE LEFT FOR VALUES
(
’ 20080131 23 : 59 : 59.997 ’,’ 20080229 23 : 59 : 59.997 ’,’ 20080331 23 : 59 : 59.997 ’
)
go
-- 增加文件组
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200801’,FILENAME = N’c:tb_P200801.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200801
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200802’,FILENAME = N’c:tb_P200802.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200802
ALTER DATABASE dbPartitionTest ADD FILE (NAME = N’P200803’,FILENAME = N’c:tb_P200803.ndf’,SIZE = 1MB,MAXSIZE = 500MB,FILEGROWTH = 1MB) TO FILEGROUP P200803
go
-- 分区架构
CREATE PARTITION SCHEME part_YearScheme AS PARTITION part_Year TO (P200801,P200802,P200803, [ PRIMARY ] )
go
CREATE TABLE [ dbo ] .t_part
(name varchar ( 100 ) default newid (),date datetime NOT NULL )
ON part_YearScheme (date)
go
-- 添加测试数据,每天1条
declare @date datetime
set @date = ’ 2007 - 12 - 31 ’
while @date <= ’ 2008 - 04 - 0 ’
1 begin
insert into t_part(date) values ( @date )
set @date = @date + 1
end
go
-- 查询数据分布在哪些分区
select $partition.part_Year(date) as 分区编号, * from t_part order by date
-- 查询数据库文件
go
sp_helpfile