创建分区表过程

创建流程：

创建代码：

 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 DateTime
15 )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 GO
12 CREATE PARTITION SCHEME PS_date
13 AS PARTITION PF_date
14 all TO ([primary])
15 
16 
17 
18   
19 create table gx_master
20 (
21 id int identity(1,1) ,
22 sex int,
23 name varchar(200),
24 reportTime datetime not null
25 ) on PS_date(reportTime)
26 
27 alter table gx_master
28 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_master
39 select 1,'a','20150505' union all
40 select 1,'a1','20150505' union all
41 select 1,'b','20150405' union all
42 select 2,'c','20150305' union all
43 select 2,'d','20150205' union all
44 select 2,'e','20150105' union all
45 select 1,'f','20141205' union all
46 select 1,'g','20141205' union all
47 
48 select 1,'aa','20150501' union all
49 select 1,'bb','20150401' union all
50 select 2,'cc','20150301' union all
51 select 2,'dd','20150201' union all
52 select 2,'ee','20150101' union all
53 select 1,'ff','20141201' union all
54 select 1,'gg','20141201'  
55 
56 insert into gx_master
57 select 1,'aaa','20150501'  
58 
59 select partition_id,a.object_id,OBJECT_NAME(a.object_id),a.partition_number,a.row_count
60 from sys.dm_db_partition_stats a
61 join sys.objects b
62 on a.object_id=b.object_id
63 where b.type='U' and a.index_id<2 
64 
65 drop table gx_master_slave
66 
67 create table gx_master_slave
68 (
69 id int identity(1,1) ,
70 sex int,
71 name varchar(200),
72 reportTime datetime not null
73 )   --on PS_date(reportTime)
74 
75 alter table gx_master_slave
76 add constraint PK_gx_master_slave primary key   (id,reportTime)
77 
78 
79 alter table gx_master
80 SWITCH  PARTITION 1  TO gx_master_slave 
81 
82 select *
83 from gx_master_slave

View Code

自动维护分区方案：

  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_values
101 where cast(value as DATe)>='2015-03-01'
102 
103 select * from sys.partition_parameters
104 
105 select *
106 from sys.partitions
107 
108 
109 select *
110 from sys.objects
111     where object_id=object_id('gx_master_slave') 
112 
113    select *
114     from sys.dm_db_partition_stats
115     where object_id=object_id('gx_master') and index_id<2
116     
117     select *
118     from sys.indexes
119        where object_id=object_id('gx_master') 
120            select *
121     from sys.indexes
122        where object_id=object_id('gx_master_slave') 
123        
124            select *
125     from  sys.indexes a
126     join 
127     (
128         select data_space_id,a.function_id,a.name as fun_name,b.name as sc_name,fanout
129         from sys.partition_functions a
130         join sys.partition_schemes b
131         on a.function_id=b.function_id
132     ) b
133     on a.data_space_id=b.data_space_id
134     where a.index_id<2 and  object_id=object_id('gx_master')
135     
136     select *
137     from sys.index_columns
138     where  object_id=object_id('gx_master')
139     
140  

View Code