SQL2005如果数据量超过1000万的话如何去分区？

http://topic.csdn.net/u/20090611/10/04b35826-8ecf-4de0-871a-0d041b4bec26.html?30934

解决方案 »

  1.   

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

  2.   

    --=========================================
    -- 移动分区表数据
    --=========================================
    -- 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