27,579
社区成员
发帖
与我相关
我的任务
分享
--创建分区函数
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'
ALTER TABLE mobileTest
ADD
PRIMARY KEY NONCLUSTERED(id,createTime)
ON OrderDatePScheme(createTime)
GO