当我创建分区表导入数据后
- SQL code
--创建分区函数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 OrdersRangeINSERT INTO OrdersRange SELECT ui.mobile,ui.imei,ui.createTime,ui.pro FROM mobileUserInfo ui GOselect $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 partitionSELECT * 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'
查询能够看到明显的效果，但是当我直接在原有数据的表中分区时，怎么就看不到效果呢，应该怎么做
- SQL code
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'
------解决方案--------------------
------解决方案--------------------
新表中，很明显，你是将表进行分区处理，但是原始表中不是，你只是修改了非聚集索引，使其使用分区，就是说你只是将索引进行了分区，因为非聚集索引的最下层并不是数据，所以你的表数据并没有进行分区。自然分区的效果就体现不出来