sql server针对表增加新的分区

一、查询当前的分区情况
select
      convert(varchar(50), ps.name) as partition_scheme,
      p.partition_number,
      ds2.name as filegroup,
      convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
      str(p.rows, 9) as rows,ps.function_id
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 sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
--WHERE i.object_id = object_id('PartitionedTable')
--and i.index_id in (0, 1)
order by partition_scheme,p.partition_number; partition_scheme partition_number   filegroup         range_boundary          rows     function_id
my_scheme                 1   my_filegroup_2         2014-06-30 00:00:00   1087047          65537
my_scheme                 2   my_filegroup_3         2014-09-30 00:00:00  10537245          65537
my_scheme                 3   my_filegroup_4         2014-12-31 00:00:00   8098550          65537
my_scheme                 4   my_filegroup_5                               203098          65537
通过查询，发现2015年只有第一季度的分区，所以把二三四季度都加进去！以第季度为例！ 二、添加分区 1、增加分区的文件组
ALTER DATABASE [mydatabase] ADD FILEGROUP [my_filegroup_6]; 2、增加该组的文件
ALTER DATABASE [mydatabase] ADD FILE ( NAME = N'my_filegroup_6', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\my_filegroup_6.ndf' , SIZE = 128MB , FILEGROWTH = 1024KB ) TO FILEGROUP [my_filegroup_6]; 3、确定下一个分区使用的文件组
alter partition scheme my_scheme next used [my_filegroup_6]; 4、调整分区范围值
select * from sys.partition_functions where function_id = 65537   可以查询分区函数
alter partition function  my_function() split range('2015/04/01') 三、再次查看
my_scheme 1 my_filegroup_2 2014-06-30 00:00:00   1087047 65537
my_scheme 2 my_filegroup_3 2014-09-30 00:00:00  10537245 65537
my_scheme 3 my_filegroup_4 2014-12-31 00:00:00   8098550 65537
my_scheme 4 my_filegroup_6 2015-04-01 00:00:00    203098 65537
my_scheme 5 my_filegroup_5                            0 65537
这样可以看到新一个分区已经建好了！ 四、说明
sql server不支持复合分区，同时做起来也相当复杂，一点也不人性话。总的说来sql server在分区方面做得不如人意呀，与oracle相比还有几十年的差距呀！
几个重要的视图
select * from sys.partition_functions  
select * from sys.partition_range_values
select * from sys.partition_schemes