当某张数据表数据量较大时,我们就需要对该表进行分区处理,以下sql语句,会将数据表按月份,分为12个分区表存储数据,废话不多说,直接上脚本:
use [SIT_L_TMS]
--开启 XP_CMDSHELL;开启创建文件夹权限
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO
--数据库名
declare @servername varchar(20) ;
-- 变量赋值
set @servername = 'SIT_L_TMS' ;
--待执行sql
declare @sql varchar(1024) ;
--设置年
declare @year varchar(10);
set @year = CONVERT(varchar(4), GETDATE(), 23);
--月数
declare @summonths int;
set @summonths = 12;
--文件组存放路径
declare @file_path varchar(200);
set @file_path = 'F:\db_group\' + @year;
--判断文件夹是否存在,不存在则创建
declare @TEMP TABLE(A INT,B INT,C INT);--建立虚拟表,用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @file_path;
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1)
BEGIN
--XP_CMDSHELL不允许使用变量拼接,所以使用exec方法
declare @EX NVARCHAR(255)
SET @EX = 'EXEC XP_CMDSHELL ''MKDIR ' + @file_path + '''';
EXEC(@EX)
END
--文件组名
declare @filegroupname varchar(50);
--文件组路径:完整路径,含文件名
declare @filegrouppath varchar(300);
--分区函数
declare @partition_func varchar(128) ;
set @partition_func = 'l_tms_partition_func';
--分区结构
declare @partition_scheme varchar(128) ;
set @partition_scheme = 'l_tms_partition_scheme';
--时间
declare @datetime varchar(10);
--按时间分区语句
declare @fuction_sql varchar(1024);
--按时间分区结构
declare @scheme_sql varchar(1024);
--建立12个分区
declare @i int;
set @i = 1;
while @i<(@summonths+1)
begin
declare @date varchar(10)
if @i < 10
begin
set @date = '0' + convert(varchar,@i);
end
else
begin
set @date = convert(varchar,@i);
end
-- 添加文件组名;文件组名按日期:例如20180401
set @datetime = @year + @date;
set @filegroupname = 'Group' + @year + @date;
set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroupname;
print @sql;
exec(@sql);
--将文件组名与文件关联
set @filegrouppath = @file_path + '\' + @filegroupname + '.ndf';
set @sql = 'alter database '+ @servername + ' add file (name=N'''+ @filegroupname +''',filename=N'''+ @filegrouppath +''',size=5Mb,filegrowth=5mb) to filegroup '+ @filegroupname;
print @sql;
exec(@sql);
--拼接按时间分区函数/拼接按时间分区结构
if @i = 1
begin
set @fuction_sql = '''' + @datetime + '01 23:59:59' + '''' + ',';
set @scheme_sql = '['+ @filegroupname +']' + ',';
end
else if @i = 12
begin
set @fuction_sql = @fuction_sql + '''' + @datetime + '01 23:59:59' + '''';
set @scheme_sql = @scheme_sql + '['+ @filegroupname +']';
end
else
begin
set @fuction_sql = @fuction_sql + '''' + @datetime + '01 23:59:59' + '''' + ',';
set @scheme_sql = @scheme_sql + '['+ @filegroupname +']' + ',';
end
set @i = @i + 1;
end
-- 创建分区函数
set @sql = 'create partition function '+ @partition_func +'(DATETIME) as range left for values('+ @fuction_sql+')';
print @sql;
exec(@sql);
-- 创建分区结构(将分区函数和分区组对应起来)
set @sql = 'create partition scheme '+ @partition_scheme +' as partition '+ @partition_func +' to(' + @scheme_sql + ',[Primary])';
print @sql;
exec(@sql);
--将普通表转换为分区表
--删除主键
ALTER TABLE [dbo].[TMS_RealTimeEqptInfo] DROP constraint PK_TMS_RealTimeEqptInfo
--创建主键,但不设为聚集索引
ALTER TABLE [dbo].[TMS_RealTimeEqptInfo] ADD constraint PK_TMS_RealTimeEqptInfo PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)ON [PRIMARY]
--创建一个新的聚集索引,并在该聚集索引中使用分区方案;注意修改分区方案的名字,按字段WriteTime进行分区
CREATE CLUSTERED INDEX CT_RealTimeData on TMS_RealTimeEqptInfo([WriteTime])
ON etm_partition_scheme([WriteTime])
数据库名称和数据表名称自行对应修改。