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