-- 进行演示操作前, 先备份, 以便可以在演示完成后, 恢复到原始状态
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' EN