创建分区表过程

创建流程：

创建代码：

--创建Partition Function并制定分区规则样例
Create Partition Function PF_OnCreateDate(datetime) 
As Range Right For Values('20141001','20141101','20141201','20150101','20150201');

--基于Partition Function创建Partition Schemen并指定分区文件组存放位置
Create Partition Scheme PS_OnCreateDate 
As Partition PF_OnCreateDate 
All To ([Primary]);

--基于Partition Scheme创建分区表，及唯一键相关（创建唯一索引必须于分区字段一起作为组合唯一索引）
Create Table TestTable(
    ID INT IDENTITY(1,1) NOT NULL,
    Tex Nvarchar(32) Not Null,
    CreateDate DateTime
)On SP_OnCreateDate(CreateDate);

-- 包含有唯一索引的索引必须与CreateDate分区字段一起创建唯一索引
Alter Table TestTable Add Constraint PK_TestTable_ID_CreateDate Primary Key(ID,CreateDate);

 以下为创建分区时的全文脚本示例：

分区表创建好后，使用sql怎么查看数据库中每个表的分区情况：

 

DBA根据特定的需要给出的方案：

use gx
go
drop table gx_master
drop table gx_master_slave

drop PARTITION SCHEME PS_date
drop PARTITION FUNCTION PF_date

CREATE PARTITION FUNCTION PF_date (datetime)
AS RANGE RIGHT FOR VALUES ( '20141101','20141201','20150101','20150201','20150301',  '20150401', '20150501');
GO
CREATE PARTITION SCHEME PS_date
AS PARTITION PF_date
all TO ([primary])



  
create table gx_master
(
id int identity(1,1) ,
sex int,
name varchar(200),
reportTime datetime not null
) on PS_date(reportTime)

alter table gx_master
add constraint PK_gx_master primary key   (id,reportTime)
 

create unique index indx_gx_master on gx_master(name,reportTime)

create  index indx_gx_master1 on gx_master(sex)

 


insert into gx_master
select 1,'a','20150505' union all
select 1,'a1','20150505' union all
select 1,'b','20150405' union all
select 2,'c','20150305' union all
select 2,'d','20150205' union all
select 2,'e','20150105' union all
select 1,'f','20141205' union all
select 1,'g','20141205' union all

select 1,'aa','20150501' union all
select 1,'bb','20150401' union all
select 2,'cc','20150301' union all
select 2,'dd','20150201' union all
select 2,'ee','20150101' union all
select 1,'ff','20141201' union all
select 1,'gg','20141201'  

insert into gx_master
select 1,'aaa','20150501'  

select partition_id,a.object_id,OBJECT_NAME(a.object_id),a.partition_number,a.row_count
from sys.dm_db_partition_stats a
join sys.objects b
on a.object_id=b.object_id
where b.type='U' and a.index_id<2 

drop table gx_master_slave

create table gx_master_slave
(
id int identity(1,1) ,
sex int,
name varchar(200),
reportTime datetime not null
)   --on PS_date(reportTime)

alter table gx_master_slave
add constraint PK_gx_master_slave primary key   (id,reportTime)


alter table gx_master
SWITCH  PARTITION 1  TO gx_master_slave 

select *
from gx_master_slave

自动维护分区方案：

if exists(select 1 from sys.objects where name='gx_P_split')
    drop proc gx_P_split
go
create proc gx_P_split
(
@tableName sysname,
@num int=null,--保留分区个数
@splitValue varchar(200)=null--分区值
)
as
begin
    set nocount on
    declare @sql nvarchar(max)
    declare @fact_num int
    declare @function_id int
    declare @function_name nvarchar(100)
    declare @scheme_name nvarchar(100)
    set @num=ISNULL(@num,7)--默认6个分区
    set @splitValue=ISNULL(@splitValue, CONVERT(varchar(7),getdate(),120)+'-01')--默认分一个当天的出来
    
    select @fact_num=fanout,@function_id=function_id,@scheme_name=sc_name,@function_name=fun_name
    from  sys.indexes a
    join 
    (
        select data_space_id,a.function_id,a.name as fun_name,b.name as sc_name,fanout
        from sys.partition_functions a
        join sys.partition_schemes b
        on a.function_id=b.function_id
    ) b
    on a.data_space_id=b.data_space_id
    where a.index_id<2 and  object_id=object_id(@tableName)
 
    if @function_id is null
    begin
        print '不是分区表'
        return
    end
    
 

    if exists(select *    from sys.partition_range_values    where function_id=@function_id and cast(value as date)=@splitValue)
    begin
        print '已经存在分区'
        return
    end
    
 
    
    set @sql=
    '
    ALTER PARTITION SCHEME '+@scheme_name+' 
    NEXT USED [primary] 
    ;
    ALTER PARTITION FUNCTION '+@function_name+'()
    SPLIT RANGE ('''+@splitValue+''')
    ;
    '
    print(@sql)
    exec(@sql)
    print('新增分区:'+@splitValue)
    
    while @fact_num>=@num
    begin
        declare @min_value varchar(200)
        set @min_value=null
        select @min_value=CONVERT(varchar(50),value,120) from sys.partition_range_values    where function_id=@function_id and boundary_id=1
        set @sql=
            '
            ALTER PARTITION FUNCTION '+@function_name+'()
            merge RANGE ('''+@min_value+''')
            '
        set @num=@num+1
        exec(@sql)
        print('合并分区:'+@min_value)
    end
    
end


gx_P_split 'gx_master',@splitValue='20160507'

    ALTER PARTITION SCHEME PS_date 
    NEXT USED  [primary]  
    ;
    ALTER PARTITION FUNCTION PF_date()
    SPLIT RANGE ('20151101')
    ;

   select *
    from sys.dm_db_partition_stats
    where object_id=object_id('gx_master') and index_id<2

select *
from sys.partition_functions
select *
from sys.partition_schemes


select *
from sys.partition_range_values
where cast(value as DATe)>='2015-03-01'

select * from sys.partition_parameters

select *
from sys.partitions


select *
from sys.objects
    where object_id=object_id('gx_master_slave') 

   select *
    from sys.dm_db_partition_stats
    where object_id=object_id('gx_master') and index_id<2
    
    select *
    from sys.indexes
       where object_id=object_id('gx_master') 
           select *
    from sys.indexes
       where object_id=object_id('gx_master_slave') 
       
           select *
    from  sys.indexes a
    join 
    (
        select data_space_id,a.function_id,a.name as fun_name,b.name as sc_name,fanout
        from sys.partition_functions a
        join sys.partition_schemes b
        on a.function_id=b.function_id
    ) b
    on a.data_space_id=b.data_space_id
    where a.index_id<2 and  object_id=object_id('gx_master')
    
    select *
    from sys.index_columns
    where  object_id=object_id('gx_master')