优化SQLServer–表和索引的分区(二)

–****************** –1.创建分区函数             –******************

–Create the partition function: dailyPF            DECLARE @StartDay DATE=DATEADD(dd,-3,CAST(SYSDATETIME() AS DATE));             CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))                 AS RANGE RIGHT FOR VALUES                 (@StartDay, DATEADD(dd,1,@StartDay), DATEADD(dd,2,@StartDay),          DATEADD(dd,3,@StartDay), DATEADD(dd,4,@StartDay) );             GO

–******************            –2. 创建文件组

–******************

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1            GO             ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2             GO             ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3             GO             ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG4             GO             ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG5             GO             ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG6             GO

–******************           –3. 创建分区架构            –******************

—           CREATE PARTITION SCHEME DailyPS                 AS PARTITION DailyPF                TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6);

–******************            –4. 在分区架构上建表            –******************

if OBJECT_ID(‘OrdersDaily’,’U’) is null            CREATE TABLE OrdersDaily (                OrderDate DATETIME2(0) NOT NULL,                OrderId int IDENTITY NOT NULL,                OrderName nvarchar(256) NOT NULL            ) on DailyPS(OrderDate)            GO

–*******************************           –创建架构            –*******************************

–Create a schema for “partition helper” objects           CREATE SCHEMA [ph] AUTHORIZATION dbo;            GO

–插入测试数据           INSERT OrdersDaily(OrderDate, OrderName)             SELECT DATEADD(ss, t.N, DATEADD(dd,-3,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,                CASE WHEN t.N % 3 = 0 THEN ‘Robot’ WHEN t.N % 4 = 0 THEN ‘Badger’  ELSE ‘Pen’ END AS OrderName            FROM ph.tally AS t–tally是一个1到10万自增长的表，只有一个字段 N

WHERE N < = 1000;               

INSERT OrdersDaily(OrderDate, OrderName)            SELECT DATEADD(ss, t.N, DATEADD(dd,-2,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,                CASE WHEN t.N % 3 = 0 THEN ‘Flying Monkey’ WHEN t.N % 4 = 0 THEN ‘Junebug’  ELSE ‘Pen’ END AS OrderName            FROM ph.tally AS t            WHERE N < = 2000;

INSERT OrdersDaily(OrderDate, OrderName)             SELECT DATEADD(ss, t.N, DATEADD(dd,-1,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,                CASE WHEN t.N % 2 = 0 THEN ‘Turtle’ WHEN t.N % 5 = 0 THEN ‘Eraser’  ELSE ‘Pen’ END AS OrderName            FROM ph.tally AS t            WHERE N < = 3000;

INSERT OrdersDaily(OrderDate, OrderName)             SELECT DATEADD(ss, t.N, CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0))) AS OrderDate,                CASE WHEN t.N % 3 = 0 THEN ‘Lasso’ WHEN t.N % 2 = 0 THEN ‘Cattle Prod’  ELSE ‘Pen’ END AS OrderName            FROM ph.tally AS t            WHERE N < = 4000;            GO

随即在创建相关的索引

–******************           –7. 创建索引            –******************            –添加聚集索引            ALTER TABLE OrdersDaily            ADD CONSTRAINT PKOrdersDaily                PRIMARY KEY CLUSTERED(OrderDate,OrderId)            GO

–对齐索引            —            CREATE NONCLUSTERED INDEX NCOrderIdOrdersDaily                 ON OrdersDaily(OrderId)            GO

–非对齐索引            CREATE NONCLUSTERED INDEX NCOrderNameOrdersDailyNonAligned                 ON OrdersDaily(OrderName) ON [PRIMARY]            GO

SELECT  SCHEMA_NAME(so.schema_id) AS schema_name ,                   OBJECT_NAME(p.object_id) AS object_name ,                    p.partition_number ,                    p.data_compression_desc ,                    dbps.row_count ,                    dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,                    si.index_id ,                    CASE WHEN si.index_id = 0 THEN ‘(heap!)’                            ELSE si.name                    END AS index_name ,                    si.is_unique ,                    si.data_space_id ,                    mappedto.name AS mapped_to_name ,                    mappedto.type_desc AS mapped_to_type_desc ,                    partitionds.name AS partition_filegroup ,                    pf.name AS pf_name ,                    pf.type_desc AS pf_type_desc ,                    pf.fanout AS pf_fanout ,                    pf.boundary_value_on_right ,                    ps.name AS partition_scheme_name ,                    rv.value AS range_value            FROM    sys.partitions p            JOIN    sys.objects so                    ON p.object_id = so.object_id                        AND so.is_ms_shipped = 0            LEFT JOIN sys.dm_db_partition_stats AS dbps                    ON p.object_id = dbps.object_id                        AND p.partition_id = dbps.partition_id            JOIN    sys.indexes si                    ON p.object_id = si.object_id                        AND p.index_id = si.index_id            LEFT JOIN sys.data_spaces mappedto                    ON si.data_space_id = mappedto.data_space_id            LEFT JOIN sys.destination_data_spaces dds                    ON si.data_space_id = dds.partition_scheme_id                        AND p.partition_number = dds.destination_id            LEFT JOIN sys.data_spaces partitionds                    ON dds.data_space_id = partitionds.data_space_id            LEFT JOIN sys.partition_schemes AS ps                    ON dds.partition_scheme_id = ps.data_space_id            LEFT JOIN sys.partition_functions AS pf                    ON ps.function_id = pf.function_id            LEFT JOIN sys.partition_range_values AS rv                    ON pf.function_id = rv.function_id                        AND dds.destination_id = CASE pf.boundary_value_on_right                                                    WHEN 0 THEN rv.boundary_id                                                    ELSE rv.boundary_id + 1                                                END