博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
创建分区表过程
阅读量:5751 次
发布时间:2019-06-18

本文共 6587 字,大约阅读时间需要 21 分钟。

创建流程:

创建代码:

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
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_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
View Code

 

转载地址:http://lkkkx.baihongyu.com/

你可能感兴趣的文章
xss test
查看>>
也谈svn分支与合并
查看>>
显式锁(第十三章)
查看>>
微软超融合私有云测试29-SCDPM2016部署之创建保护组备份(备份虚拟机)
查看>>
LBS“他爹”GIS
查看>>
同质化倾向的互联网金融 玖富带来了温度与色彩
查看>>
SCCM的证书配置PKI
查看>>
看linux书籍做的一些重要笔记(2011.07.03更新)
查看>>
Exchange server 2010系列教程之一 安装Exchange 2010准备条件
查看>>
POI 生成 xls 文件使用总结(快速入门)
查看>>
CString、Char* ,char [20]、wchar_t、unsigned short转化
查看>>
从案例学RxAndroid开发(上)
查看>>
debian 下安装megacli
查看>>
我写的第一个shell脚本(2009-06-08)
查看>>
ubutun 中 Eclipse中 快捷键 Alt + / 不能使用的问题
查看>>
Redis学习手册(内存优化)
查看>>
浅尝TensorFlow on Kubernetes
查看>>
wnmp-3.1.0安装cakephp启动失败处理
查看>>
springboot系列十 Spring-Data-Redis
查看>>
Confluence 6 注册外部小工具
查看>>