创建流程：
创建代码：
1 --创建Partition Function并制定分区规则样例 2 Create Partition Function PF_OnCreateDate(datetime) 3 As Range Right For Values('20141001','20141101','20141201','20150101','20150201'); 4 5 --基于Partition Function创建Partition Schemen并指定分区文件组存放位置 6 Create Partition Scheme PS_OnCreateDate 7 As Partition PF_OnCreateDate 8 All To ([Primary]); 9 10 --基于Partition Scheme创建分区表，及唯一键相关（创建唯一索引必须于分区字段一起作为组合唯一索引）11 Create Table TestTable(12 ID INT IDENTITY(1,1) NOT NULL,13 Tex Nvarchar(32) Not Null,14 CreateDate DateTime15 )On SP_OnCreateDate(CreateDate);16 17 -- 包含有唯一索引的索引必须与CreateDate分区字段一起创建唯一索引18 Alter Table TestTable Add Constraint PK_TestTable_ID_CreateDate Primary Key(ID,CreateDate); 以下为创建分区时的全文脚本示例：
分区表创建好后，使用sql怎么查看数据库中每个表的分区情况：
DBA根据特定的需要给出的方案：
1 use gx 2 go 3 drop table gx_master 4 drop table gx_master_slave 5 6 drop PARTITION SCHEME PS_date 7 drop PARTITION FUNCTION PF_date 8 9 CREATE PARTITION FUNCTION PF_date (datetime)10 AS RANGE RIGHT FOR VALUES ( '20141101','20141201','20150101','20150201','20150301', '20150401', '20150501');11 GO12 CREATE PARTITION SCHEME PS_date13 AS PARTITION PF_date14 all TO ([primary])15 16 17 18 19 create table gx_master20 (21 id int identity(1,1) ,22 sex int,23 name varchar(200),24 reportTime datetime not null25 ) on PS_date(reportTime)26 27 alter table gx_master28 add constraint PK_gx_master primary key (id,reportTime)29 30 31 create unique index indx_gx_master on gx_master(name,reportTime)32 33 create index indx_gx_master1 on gx_master(sex)34 35 36 37 38 insert into gx_master39 select 1,'a','20150505' union all40 select 1,'a1','20150505' union all41 select 1,'b','20150405' union all42 select 2,'c','20150305' union all43 select 2,'d','20150205' union all44 select 2,'e','20150105' union all45 select 1,'f','20141205' union all46 select 1,'g','20141205' union all47 48 select 1,'aa','20150501' union all49 select 1,'bb','20150401' union all50 select 2,'cc','20150301' union all51 select 2,'dd','20150201' union all52 select 2,'ee','20150101' union all53 select 1,'ff','20141201' union all54 select 1,'gg','20141201' 55 56 insert into gx_master57 select 1,'aaa','20150501' 58 59 select partition_id,a.object_id,OBJECT_NAME(a.object_id),a.partition_number,a.row_count60 from sys.dm_db_partition_stats a61 join sys.objects b62 on a.object_id=b.object_id63 where b.type='U' and a.index_id<2 64 65 drop table gx_master_slave66 67 create table gx_master_slave68 (69 id int identity(1,1) ,70 sex int,71 name varchar(200),72 reportTime datetime not null73 ) --on PS_date(reportTime)74 75 alter table gx_master_slave76 add constraint PK_gx_master_slave primary key (id,reportTime)77 78 79 alter table gx_master80 SWITCH PARTITION 1 TO gx_master_slave 81 82 select *83 from gx_master_slave
自动维护分区方案：
1 if exists(select 1 from sys.objects where name='gx_P_split') 2 drop proc gx_P_split 3 go 4 create proc gx_P_split 5 ( 6 @tableName sysname, 7 @num int=null,--保留分区个数 8 @splitValue varchar(200)=null--分区值 9 ) 10 as 11 begin 12 set nocount on 13 declare @sql nvarchar(max) 14 declare @fact_num int 15 declare @function_id int 16 declare @function_name nvarchar(100) 17 declare @scheme_name nvarchar(100) 18 set @num=ISNULL(@num,7)--默认6个分区 19 set @splitValue=ISNULL(@splitValue, CONVERT(varchar(7),getdate(),120)+'-01')--默认分一个当天的出来 20 21 select @fact_num=fanout,@function_id=function_id,@scheme_name=sc_name,@function_name=fun_name 22 from sys.indexes a 23 join 24 ( 25 select data_space_id,a.function_id,a.name as fun_name,b.name as sc_name,fanout 26 from sys.partition_functions a 27 join sys.partition_schemes b 28 on a.function_id=b.function_id 29 ) b 30 on a.data_space_id=b.data_space_id 31 where a.index_id<2 and object_id=object_id(@tableName) 32 33 if @function_id is null 34 begin 35 print '不是分区表' 36 return 37 end 38 39 40 41 if exists(select * from sys.partition_range_values where function_id=@function_id and cast(value as date)=@splitValue) 42 begin 43 print '已经存在分区' 44 return 45 end 46 47 48 49 set @sql= 50 ' 51 ALTER PARTITION SCHEME '+@scheme_name+' 52 NEXT USED [primary] 53 ; 54 ALTER PARTITION FUNCTION '+@function_name+'() 55 SPLIT RANGE ('''+@splitValue+''') 56 ; 57 ' 58 print(@sql) 59 exec(@sql) 60 print('新增分区:'+@splitValue) 61 62 while @fact_num>=@num 63 begin 64 declare @min_value varchar(200) 65 set @min_value=null 66 select @min_value=CONVERT(varchar(50),value,120) from sys.partition_range_values where function_id=@function_id and boundary_id=1 67 set @sql= 68 ' 69 ALTER PARTITION FUNCTION '+@function_name+'() 70 merge RANGE ('''+@min_value+''') 71 ' 72 set @num=@num+1 73 exec(@sql) 74 print('合并分区:'+@min_value) 75 end 76 77 end 78 79 80 gx_P_split 'gx_master',@splitValue='20160507' 81 82 ALTER PARTITION SCHEME PS_date 83 NEXT USED [primary] 84 ; 85 ALTER PARTITION FUNCTION PF_date() 86 SPLIT RANGE ('20151101') 87 ; 88 89 select * 90 from sys.dm_db_partition_stats 91 where object_id=object_id('gx_master') and index_id<2 92 93 select * 94 from sys.partition_functions 95 select * 96 from sys.partition_schemes 97 98 99 select *100 from sys.partition_range_values101 where cast(value as DATe)>='2015-03-01'102 103 select * from sys.partition_parameters104 105 select *106 from sys.partitions107 108 109 select *110 from sys.objects111 where object_id=object_id('gx_master_slave') 112 113 select *114 from sys.dm_db_partition_stats115 where object_id=object_id('gx_master') and index_id<2116 117 select *118 from sys.indexes119 where object_id=object_id('gx_master') 120 select *121 from sys.indexes122 where object_id=object_id('gx_master_slave') 123 124 select *125 from sys.indexes a126 join 127 (128 select data_space_id,a.function_id,a.name as fun_name,b.name as sc_name,fanout129 from sys.partition_functions a130 join sys.partition_schemes b131 on a.function_id=b.function_id132 ) b133 on a.data_space_id=b.data_space_id134 where a.index_id<2 and object_id=object_id('gx_master')135 136 select *137 from sys.index_columns138 where object_id=object_id('gx_master')139 140