-- 進(jìn)行演示操作前, 先備份, 以便可以在演示完成后, 恢復(fù)到原始狀態(tài)
USE master
-- 備份
BACKUP DATABASE AdventureWorks
TO DISK = 'AdventureWorks.bak'
WITH FORMAT
---- 恢復(fù)
--RESTORE DATABASE AdventureWorks
-- FROM DISK = 'AdventureWorks.bak'
-- WITH REPLACE
GO
--=========================================
-- 轉(zhuǎn)換為分區(qū)表
--=========================================
USE AdventureWorks
GO
-- 1. 創(chuàng)建分區(qū)函數(shù)
-- a. 適用于存儲歷史存檔記錄的分區(qū)表的分區(qū)函數(shù)
DECLARE @dt datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
@dt,
DATEADD(Year, 1, @dt))
-- b. 適用于存儲歷史記錄的分區(qū)表的分區(qū)函數(shù)
--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. 創(chuàng)建分區(qū)架構(gòu)
-- a. 適用于存儲歷史存檔記錄的分區(qū)表的分區(qū)架構(gòu)
CREATE PARTITION SCHEME PS_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])
-- b. 適用于存儲歷史記錄的分區(qū)表的分區(qū)架構(gòu)
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. 轉(zhuǎn)換為分區(qū)表
-- a. 將存儲歷史存檔記錄的表轉(zhuǎn)換為分區(qū)表
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH(
MOVE TO PS_HistoryArchive(TransactionDate))
-- b.將存儲歷史記錄的表轉(zhuǎn)換為分區(qū)表
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH(
MOVE TO PS_History(TransactionDate))
GO
-- 5. 恢復(fù)主鍵
-- a. 恢復(fù)存儲歷史存檔記錄的分區(qū)表的主鍵
ALTER TABLE Production.TransactionHistoryArchive
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
-- b. 恢復(fù)存儲歷史記錄的分區(qū)表的主鍵
ALTER TABLE Production.TransactionHistory
ADD CONSTRAINT PK_TransactionHistory_TransactionID
PRIMARY KEY CLUSTERED(
TransactionID,
TransactionDate)
GO
-- 6. 恢復(fù)索引
-- a. 恢復(fù)存儲歷史存檔記錄的分區(qū)表的索引
CREATE INDEX IX_TransactionHistoryArchive_ProductID
ON Production.TransactionHistoryArchive(
ProductID)
CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistoryArchive(
ReferenceOrderID,
ReferenceOrderLineID)
-- b. 恢復(fù)存儲歷史記錄的分區(qū)表的索引
CREATE INDEX IX_TransactionHistory_ProductID
ON Production.TransactionHistory(
ProductID)
CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ON Production.TransactionHistory(
ReferenceOrderID,
ReferenceOrderLineID)
GO
-- 7. 查看分區(qū)表的相關(guān)信息
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
--=========================================
-- 移動分區(qū)表數(shù)據(jù)
--=========================================
-- 1. 為存儲歷史存檔記錄的分區(qū)表增加分區(qū), 并接受從歷史記錄分區(qū)表移動過來的數(shù)據(jù)
-- a. 修改分區(qū)架構(gòu), 增加用以接受新分區(qū)的文件組
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
-- b. 修改分區(qū)函數(shù), 增加分區(qū)用以接受從歷史記錄分區(qū)表移動過來的數(shù)據(jù)
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
-- c. 將歷史記錄表中的過期數(shù)據(jù)移動到歷史存檔記錄表中
ALTER TABLE Production.TransactionHistory
SWITCH PARTITION 2
TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)
-- d. 將接受到的數(shù)據(jù)與原來的分區(qū)合并
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
GO
-- 2. 將存儲歷史記錄的分區(qū)表中不包含數(shù)據(jù)的分區(qū)刪除, 并增加新的分區(qū)以接受新數(shù)據(jù)
-- a. 合并不包含數(shù)據(jù)的分區(qū)
DECLARE @dt datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)
-- b. 修改分區(qū)架構(gòu), 增加用以接受新分區(qū)的文件組
ALTER PARTITION SCHEME PS_History
NEXT USED [PRIMARY]
-- c. 修改分區(qū)函數(shù), 增加分區(qū)用以接受新數(shù)據(jù)
SET @dt = '20041001'
ALTER PARTITION FUNCTION PF_History()
SPLIT RANGE(@dt)
GO
--=========================================
-- 清除歷史存檔記錄中的過期數(shù)據(jù)
--=========================================
-- 1. 創(chuàng)建用于保存過期的歷史存檔數(shù)據(jù)的表
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. 將數(shù)據(jù)從歷史存檔記錄分區(qū)表移動到第1步創(chuàng)建的表中
ALTER TABLE Production.TransactionHistoryArchive
SWITCH PARTITION 1
TO Production.TransactionHistoryArchive_2001_temp
-- 3. 刪除不再包含數(shù)據(jù)的分區(qū)
DECLARE @dt datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
-- 4. 修改分區(qū)架構(gòu), 增加用以接受新分區(qū)的文件組
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
-- 5. 修改分區(qū)函數(shù), 增加分區(qū)用以接受新數(shù)據(jù)
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)
查詢分區(qū)信息：
;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
--測試數(shù)據(jù)庫
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
--分區(qū)函數(shù)
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
--分區(qū)架構(gòu)
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
--添加測試數(shù)據(jù),每天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
--查詢數(shù)據(jù)分布在哪些分區(qū)
select $partition.part_Year(date) as 分區(qū)編號,* from t_part order by date
--查詢數(shù)據(jù)庫文件
go
sp_helpfileposted on 2011-04-20 16:03
SIMONE 閱讀(1669)
評論(0) 編輯 收藏 所屬分類:
SQL SERVER