--------------------------------------这里尽可能模拟楼主的情况-----------------------------------
--------------------------------------一切以说明问题为主，不钻牛角尖-----------------------------------------
-----------------------------------一切以数据来说明问题-----------------------------------------



 --创建分区函数，300W一个分区，计划插入1500W数据做测试
 create partition function MyPartitonFun1(int)  
 as range right for values(3000000,6000000,9000000,12000000,15000000);  
 
 --创建分区方案
create partition scheme MyPartitonScheme1 
as partition MyPartitonFun1   
all to ([primary])

--建表，分区表
create table TestWithPartition (  
  ID1 int identity(1,1),
  ID2 decimal(18,5),
  Col3 decimal(18,5),
  Col4 decimal(18,5)
 )on MyPartitonScheme1(ID1) 
  
 --建表，没有分区的表
 create table TestWithNoPartition (  
  ID1 int identity(1,1),
  ID2 decimal(18,5),
  Col3 decimal(18,5),
  Col4 decimal(18,5)
 ) 
 
 
 --写入数据，温馨提示，
 --这里的ID1的意图是为了模拟楼主所说的“有规律增长”，模拟楼主的情况，并不是特意搞的自增列
 --下班开始搞，插入到700W多的时候sqlserver报错，神马.net freamwork异常，之后重启sqlserver继续写
 --总数据是1400W多，将近1500W
 declare @i int
 set @i=0
 while @i<15000000
 begin
    insert into TestWithPartition(Col3,Col4) values (rand()*10000000000,rand()*10000000000)
    insert into TestWithNoPartition(Col3,Col4) values (rand()*10000000000,rand()*10000000000)
    set @i=@i+1
 end
 
 
--更新ID2，模拟楼主的情况
update TestWithPartition set ID2=ID1+rand() where  ID2 is null
update TestWithNoPartition set ID2=ID1+rand() where ID2 is null

--创建聚集索引
 create clustered index index_1 on TestWithPartition (ID1)
 create clustered index index_1 on TestWithNoPartition (ID1)
  
  
 --查询分区表的数据分布
select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(10), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN s