SQL Server 2005 表分区事例

SQL Server 2005 表分区事例

创建分区函数：这个分区函数是安装月份划分的

CREATE PARTITION FUNCTION [Month_Partition](int) AS RANGE
LEFT
FOR VALUES (1,2,3,4,5,6,7,8,9,10,11);

 创建分区方案：基于上面的分区函数的分区方案

CREATE PARTITION SCHEME [Month_Scheme] AS PARTITION
[Month_Partition]
ALL TO ([PRIMARY]);

创建分区表：分布键通过datepart(month,getdate())函数获得

CREATE TABLE [dbo].[PT_Date](
    [Month] [int] NOT NULL CONSTRAINT [DF_PT_Date_Month]  DEFAULT (datepart(month,getdate())),
    [Date] [datetime] NOT NULL
) ON [Month_Scheme]([Month]);

查询新建分区表中各条数据所在的分区：

SELECT $partition.[Month_Partition]([Month]) as P, * FROM PT_Date

映射到新建表的分区：

SELECT * From sys.partitions WHERE OBJECT_NAME(object_id) = 'PT_Date'