sqlserver自动分区

最新推荐文章于 2025-06-21 10:54:04 发布

贝壳里的沙

最新推荐文章于 2025-06-21 10:54:04 发布

阅读量2.6k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： sqlserver 文章标签： sqlserver自动分区

本文链接：https://blog.youkuaiyun.com/lixiang987654321/article/details/60618219

sqlserver 专栏收录该内容

1 篇文章

订阅专栏

废话少说，直接进入步骤：

（1）创建手动分区

-- 注意：在sql server中执行该脚本，必须选中对应数据库
--       不要选择master数据库，否则创建schema失败
create database configserverV2;
 

-- 文件组变量声明
declare @servername varchar(20) ;
declare @filegroup1 varchar(64) ;
declare @filegroup2 varchar(64) ;
declare @filegroup3 varchar(64) ;
declare @sql varchar(1024) ;

declare @partition_func varchar(128) ;
declare @partition_scheme varchar(128) ;

-- 变量赋值
set @servername = 'configserverV2' ;
set @filegroup1 = 'ByTimeGroup1' ;
set @filegroup2 = 'ByTimeGroup2' ;
set @filegroup3 = 'ByTimeGroup3' ;
set @partition_func = 'alarmlog_partition_func';
set @partition_scheme = 'alarmlog_partition_scheme';

-- 分组文件路径
declare @filegname1 varchar(300) ;
declare @filegname2 varchar(300) ;
declare @filegname3 varchar(300) ;

set @filegname1 = 'D:\configserver\data\' + @filegroup1 + '.ndf' ;
set @filegname2 = 'D:\configserver\data\' + @filegroup2 + '.ndf' ;
set @filegname3 = 'D:\configserver\data\' + @filegroup3 + '.ndf' ;

-- 打印文件路径
print @filegname1;
print @filegname2;
print @filegname3;

set @sql = 'use '+ @servername;
print @sql;
exec(@sql);

-- 添加文件组名
set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroup1;
print @sql;
exec(@sql);
set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroup2;
print @sql;
exec(@sql);
set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroup3;
print @sql;
exec(@sql);

-- 将文件组与文件名绑定
set @sql = 'alter database '+ @servername + ' add file (name=N'''+@filegroup1+''',filename=N'''+@filegname1+''',size=5Mb,filegrowth=5mb) to filegroup '+@filegroup1;
print @sql;
exec(@sql);

set @sql = 'alter database '+ @servername + ' add file (name=N'''+@filegroup2+''',filename=N'''+@filegname2+''',size=5Mb,filegrowth=5mb) to filegroup '+@filegroup2;
print @sql;
exec(@sql);

set @sql = 'alter database '+ @servername + ' add file (name=N'''+@filegroup3+''',filename=N'''+@filegname3+''',size=5Mb,filegrowth=5mb) to filegroup '+@filegroup3;
print @sql;
exec(@sql);

-- 创建分区函数
set @sql = 'create partition function '+ @partition_func +'(DATETIME) as range left for values(''20161231 23:59:59'',''20170131 23:59:59'',''20170228 23:59:59'')';
print @sql;
exec(@sql);

-- 创建分区结构(将分区函数和分区组对应起来)
set @sql = 'create partition scheme '+ @partition_scheme +' as partition '+ @partition_func +' to(['+ @filegroup1 +'],['+ @filegroup2 +'],['+ @filegroup3 +'],[Primary])';
print @sql;
exec(@sql);

（2）创建自动分区job

declare @servername varchar(20) ;
declare @maxValue int;
declare @secondMaxValue int;
declare @differ int;
declare @fileGroupName varchar(200);
declare @fileNamePath varchar(200);
declare @fileName varchar(200);
declare @sql varchar(1000);
declare @filenameprefix varchar(32);
declare @filedir varchar(1024) ;

declare @partition_func varchar(128) ;
declare @partition_scheme varchar(128) ;

-- 服务器名称
set @servername = 'configserverV2' ;
-- 文件名前缀
set @filenameprefix = 'ByTimeGroup' ;
-- 文件路径
set @filedir = 'D:\configserver\data\';
-- 分区函数
set @partition_func = 'alarmlog_partition_func';
-- 分区结构
set @partition_scheme = 'alarmlog_partition_scheme';


-- 文件组
set @fileGroupName = @filenameprefix + REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120 ),'-',''),' ',''),':','');
print @fileGroupName;


-- 添加文件组
set @sql = 'alter database '+@servername+' add fileGroup '+@fileGroupName;
print @sql;
exec(@sql);


-- 文件全路径
set @fileNamePath = @filedir + @fileGroupName +'.ndf';
print @fileNamePath;
-- 文件名称
set @fileName = @fileGroupName;
print @fileName;


-- 将文件组与文件名绑定
set @sql = 'alter database '+ @servername + ' add file (name=N'''+@fileGroupName+''',filename=N'''+@fileNamePath+''',size=5Mb,filegrowth=5mb) to filegroup '+@fileGroupName;
print @sql;
exec(@sql);

-- 修改分区方案，用一个新的文件组用于存放下一新增的数据
SET @sql='alter partition scheme '+ @partition_scheme +' next used  '+@fileGroupName;
print @sql;
exec(@sql);

-- 分区架构
select @maxValue = CONVERT(INT, MAX(value)) from SYS.PARTITION_RANGE_VALUES PRV;
select @secondMaxValue = CONVERT(INT, MIN(value)) from 
(
    select top 2 * from SYS.PARTITION_RANGE_VALUES order by value desc
) PRV ;

set @differ = @maxValue - @secondMaxValue;

-- 分区函数
SET @sql= 'alter partition function ' + @partition_func  +' split range (' + @maxValue +@differ +')';
print @sql;
exec(@sql);

（3）针对2为通过获取前两次ID值的差（也就是增值）来确定新建的分区value区间，如果按照时间分区，则更简单，代码如下

declare @servername varchar(20) ;
declare @maxValue int;
declare @secondMaxValue int;
declare @differ int;
declare @fileGroupName varchar(200);
declare @fileNamePath varchar(200);
declare @fileName varchar(200);
declare @sql varchar(1000);
declare @filenameprefix varchar(32);
declare @filedir varchar(1024) ;

declare @partition_func varchar(128) ;
declare @partition_scheme varchar(128) ;
declare @cur_date varchar(128) ;

-- 服务器名称
set @servername = 'configserverV2' ;
-- 文件名前缀
set @filenameprefix = 'ByTimeGroup' ;
-- 文件路径
set @filedir = 'D:\configserver\data\';
-- 分区函数
set @partition_func = 'alarmlog_partition_func';
-- 分区结构
set @partition_scheme = 'alarmlog_partition_scheme';

-- 文件组
set @cur_date = CONVERT(varchar, GETDATE(), 120 );
set @fileGroupName = @filenameprefix + REPLACE(REPLACE(REPLACE(@cur_date,'-',''),' ',''),':','');
print @fileGroupName;

-- 添加文件组
set @sql = 'alter database '+@servername+' add fileGroup '+@fileGroupName;
print @sql;
exec(@sql);

-- 文件全路径
set @fileNamePath = @filedir + @fileGroupName +'.ndf';
print @fileNamePath;
-- 文件名称
set @fileName = @fileGroupName;
print @fileName;


-- 将文件组与文件名绑定
set @sql = 'alter database '+ @servername + ' add file (name=N'''+@fileGroupName+''',filename=N'''+@fileNamePath+''',size=5Mb,filegrowth=5mb) to filegroup '+@fileGroupName;
print @sql;
exec(@sql);

-- 修改分区方案，用一个新的文件组用于存放下一新增的数据
set @sql='alter partition scheme '+ @partition_scheme +' next used  '+@fileGroupName;
print @sql;
exec(@sql);

declare @cur_value varchar(128) ;
set @cur_value = convert(varchar,@maxValue+@differ);

-- 分区函数
set @sql= 'alter partition function ' + @partition_func  +'() split range (''' + @cur_date +''')';
print @sql;
exec(@sql);

（4）查看系统分区

select * from sys.partition_functions

（5）查看系统分区区间

select * from sys.partition_range_values

（6）查看分区结构

select * from sys.partition_schemes

（7）依据分区方案建立分区

CREATE TABLE [dbo].[test](
    [Id] [int] NULL,
    [Name] [varchar](20) NULL,
    [Password] [varchar](20) NULL,
    [CreateTime] [datetime] NULL
) ON alarmlog_partition_scheme(CreateTime)alarmlog_partition_scheme(CreateTime)

（8）查看某分区数据

SELECT * FROM [dbo].[test] WHERE $PARTITION.[alarmlog_partition_func](CreateTime)=‘2017-03-07 00:05:20’alarmlog_partition_func](CreateTime)=‘2017-03-07 00:05:20’

（9）

快来成为我的朋友或合作伙伴，一起交流，一起进步！
QQ群：961179337
微信：lixiang6153
邮箱：lixx2048@163.com
公众号：IT技术快餐
更多资料等你来拿！