sqlserver表分区解决方法

--创建分区函数
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'