SqlServer大数据的分区方案

这里介绍的是大数据量的维护日志的分区解决方案：

每个月1张数据表，1个分组文件、31个分区(按每天1个分区)。。。。

为了日后维护方便，直接删除旧的日志数据文件就可以，而不需要去做分区压缩。

 

--用的是测试数据库
--注释的脚本表示在建库的时候只需要执行一次就可以了
--use Test
--create partition function DAY_PF (tinyint)
--as range LEFT
--for values (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
--go

--下面这段注释是用来获取当前数据库的物理目录位置
--declare @filename varchar(250)
--select @filename = left(filename,charindex('\',reverse(filename))+2) from  master.dbo.sysdatabases   where   name   =   'Test'
--print @filename

--删除分区文件、分组的sql
--alter database UnIncSoft remove file [xxx];
--alter database UnIncSoft remove filegroup [xxx];

--下面才是要每次执行的脚本
--可以先运行后打印出脚本，再把脚本复制到执行窗口去运行

declare @year varchar(4)--年
declare @month varchar(2)--月
declare @tablePre varchar(50)--表名称前缀
declare @database varchar(50)--数据库名称
declare @dataBasePath varchar(250)--数据库的目录
declare @useCommand varchar(100)--use数据库
declare @sqlCommand varchar(2000)--sql命令

set @year=N'2013'
set @month=N'06'
set @tablePre=N'TestLog'
set @database = N'Test'
set @dataBasePath=N'E:\SQLDATA\Data\'

--命令开始
select @useCommand = N'USE '+@database+N';'

--创建文件组
declare @file varchar(50)
declare @filegroup varchar(50)
select @file = N'DATA_'+@database+@year+@month
select @filegroup=N'DATA_'+@database+@year+@month

select @sqlCommand = @useCommand+N'ALTER DATABASE '+@database+N' ADD FILEGROUP '+@filegroup+';
go'
print @sqlCommand
--EXECUTE sp_executesql @sql
select @sqlCommand = @useCommand+
N'ALTER DATABASE '+@database+N' ADD FILE
(NAME = N'''+@file+''',
FILENAME = N'''+@dataBasePath+@file+'.ndf'',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP '+@filegroup+';
go'
print @sqlCommand
--EXECUTE sp_executesql @sql

--创建分区方案
declare @psName varchar(50)
select @psName=N'DAY_PS'+@year+@month
select @sqlCommand = @useCommand+
N'create partition scheme '+@psName+
N' as partition DAY_PF
to (['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
['+@filegroup+'],
[PRIMARY]);
go'
print @sqlCommand
--EXECUTE sp_executesql @sql

--创建表
declare @table varchar(50)
select @table=@tablePre+@year+@month
select @sqlCommand = @useCommand+
N'create table '+@table+N'(
   autoID               bigint               identity,
   platform             tinyint              not null,
   identifier           varchar(255)         not null,
   oldVersion           varchar(128)         not null,
   oldVername           varchar(128)         not null,
   newVersion           varchar(128)         not null,
   newVername           varchar(128)         not null,
   md5                  varchar(255)         null,
   num                  bigint               not null DEFAULT((0)),
   calcDay              tinyint              not null
)
on '+@psName+'(calcDay);
go'
print @sqlCommand
--EXECUTE sp_executesql @sql

--创建索引
select @sqlCommand = @useCommand+
N'create clustered index IX_ID on '+@table +'(
autoID ASC);
go'
print @sqlCommand
--EXECUTE sp_executesql @sql

select @sqlCommand = @useCommand+
N'create nonclustered index Index_platform_num on '+@table +'(
platform,num DESC);
go'
print @sqlCommand
--EXECUTE sp_executesql @sql

select @sqlCommand = @useCommand+
N'create nonclustered index Index_p_i_vsion_n on '+@table +'(
platform,identifier,oldVersion,oldVername,newVersion,newVername,num DESC);
go'
print @sqlCommand
--EXECUTE sp_executesql @sql