SQL SERVER-分区表

--创建分区函数
CREATE PARTITION FUNCTION parfn_Date ( Date )
AS RANGE  RIGHT 
FOR VALUES ('2019-05-01','2019-06-01') 

--查看分区函数
SELECT  *  FROM  SYS.PARTITION_FUNCTIONS

--创建分区架构
CREATE PARTITION SCHEME sch_parfn_Date
AS PARTITION parfn_Date
 TO ([primary],fg1,fg2  )

 --查看分区架构
 SELECT  *  FROM  SYS.PARTITION_schemes


 --创建分区表
  CREATE  TABLE  shipment_header
 (
     [CustomerID] [varchar](20) NOT NULL,
    [SAPSoldTo] [varchar](20) NOT NULL,
    [ShipmentNo] [varchar](20) NOT NULL,
    [Shipmark] [varchar](20) NULL,
    [ShipDate] Date NULL,

 )ON  sch_parfn_Date([ShipDate])
 --指定架构和分区列，分区列的数据类型要和分区函数的参数类型相同


 --查看分区表数据的分布状态
 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 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('SHIPMENT_HEADER')
and i.index_id in (0, 1) 
order by p.partition_number

参考：https://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html