日期:2014-05-18 浏览次数:20735 次
--创建分区函数 CREATE PARTITION FUNCTION DateRangPFN(DATETIME) /* 创建了三个边界值,4个分区 分区1: 所有时间小于或等于 2011-05-01 :23.59.59.997 的数据 分区2:时间段在 2011-05-01 :23.59.59.997< date<=2011-10-01 23:59:59:997 的所有数据 分区3:时间段在 2011-10-01 23:59:59:997<date<=2012-01-01 23:59.59.997 的所有数据 分区4:时间段在 date>=2012-01-01 :23.59.59.997 的所有数据 */ AS RANGE LEFT FOR VALUES(dateadd(ms,-3,'2011-05-01'),dateadd(ms,-3,'2011-10-01'),dateadd(ms,-3,'2012-01-01')); GO --创建分区架构 CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION DateRangPFN TO ([primary],[primary],[primary],[primary]) GO --SELECT TOP 10 * FROM mobileUser1Info ui --创建分区表 CREATE TABLE OrdersRange ( id INT IDENTITY(1,1) NOT null, mobile VARCHAR(50) NOT NULL, imei VARCHAR(50) NOT NULL, createTime DATETIME NOT NULL, pro VARCHAR(50) )ON OrderDatePScheme(createTime) GO -- 创建聚集分区索引 CREATE CLUSTERED INDEX ixc_Order_createDatte ON OrdersRange(createTime) GO -- 为分区表设置主键 alter table OrdersRange add constraint PK_Orders primary key (id, imei,createTime) go --导入数据 SELECT * FROM mobileUserInfo ui --DROP TABLE OrdersRange INSERT INTO OrdersRange SELECT ui.mobile,ui.imei,ui.createTime,ui.pro FROM mobileUserInfo ui GO select $partition.DateRangPFN(createTime) AS partition ,count(*) AS rows ,min(createTime) AS minval ,max(createTime)from OrdersRange AS maxval group by $partition.DateRangPFN(createTime) order by partition SELECT * FROM mobileUserInfo WHERE createTime>='2011-10-01 00:00:01.263' AND createTime <'2011-10-30 15:35:47.063' GO SELECT * FROM OrdersRange WHERE createTime>='2011-10-01 00:00:01.263' AND createTime <'2011-10-30 15:35:47.063'
select $partition.DateRangPFN(createTime) AS partition ,count(*) AS rows ,min(createTime) AS minval ,max(createTime)from mobileTest AS maxval group by $partition.DateRangPFN(createTime) order by partition GO SELECT * FROM mobileUserInfo WHERE createTime>='2011-10-01 00:00:01.263' AND createTime <'2011-10-30 15:35:47.063' GO SELECT * FROM mobileTest WHERE createTime>='2011-10-01 00:00:01.263' AND createTime <'2011-10-30 15:35:47.063'