数据库分区实践
--查询一张表的分区
select $partition.Part_func_UserLog(createdDate) as Patition from dbo.UserLog GROUP BY $partition.Part_func_UserLog(createdDate)
--创建分区的时间段
CREATE PARTITION FUNCTION Part_func_UserLog(datetime) AS RANGE LEFT FOR VALUES ( '20120709 23:59:59.997', '20120710 23:59:59.997', '20120711 23:59:59.997', '20120712 23:59:59.997', '20120713 23:59:59.997', '20120714 23:59:59.997', '20120715 23:59:59.997', '20120716 23:59:59.997', '20120717 23:59:59.997', '20120718 23:59:59.997' );
--创建分区分组
ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks1]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks2]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks3]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks4]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks5]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks6]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks7]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks8]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks9]; GO ALTER DATABASE GoodBooks ADD FILEGROUP [FGGoodBooks10];
--创建分区文件
ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks1_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks1_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks1]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks2_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks2_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks2]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks3_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks3_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks3]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks4_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks4_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks4]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks5_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks5_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks5]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks6_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks6_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks6]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks7_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks7_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks7]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks8_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks8_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks8]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks9_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks9_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks9]; ALTER DATABASE GoodBooks ADD FILE (NAME=FGGoodBooks10_data,FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks10_data.ndf',SIZE=3MB) TO FILEGROUP [FGGoodBooks10];
--绑定分组
CREATE PARTITION SCHEME Part_func_UserLog_Scheme AS PARTITION Part_func_UserLog TO ([FGGoodBooks1],[FGGoodBooks2],[FGGoodBooks3],[FGGoodBooks4],[FGGoodBooks5],[FGGoodBooks6],[FGGoodBooks7],[FGGoodBooks8],[FGGoodBooks9],[FGGoodBooks10],[Primary]); GO
--给表添加主键
ALTER TABLE dbo.UserLog ADD CONSTRAINT PK_UserLog PRIMARY KEY NONCLUSTERED (UserLogID)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_UserLog_partitioncol ON dbo.UserLog (UserLogID)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON Part_func_UserLog_Scheme(CreatedDate)
GO
--删除表的主键、聚集索引
alter table UserLog drop CONSTRAINT PK_UserLog
--查询指定分区的数据
select * from dbo.UserLog WHERE $PARTITION.Part_func_UserLog(CreatedDate)=3
--自动添加分区
declare @databaseName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(500), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)
set @databaseName='GoodBooks'
set @newDay=CONVERT(varchar(100), getdate(), 23)--23:按天 114:按时间
set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-5,getdate()), 120 ) as datetime)
set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')
set @fileGroupName=N'G'+@newNameStr
set @ndfName=N'F'+@newNameStr+''
set @fullPath=N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@ndfName+'.ndf'
set @partFunName=N'Part_func_UserLog'
set @schemeName=N'Part_func_UserLog_Scheme'
--创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@databaseName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区方案'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
print '修改分区函数'
end
end
--创建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
PRINT 'ALTER DATABASE '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'
print '新创建ndf文件'
end
/*--------------------以上创建数据库的文件组和物理文件------------------------*/
--分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此处修改需要在修改分区函数之前执行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')')
print '新创建分区函数'
end
--分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此处修改需要在修改分区方案之前执行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
print '新创建分区方案'
end
print '---------------以下是变量定义值显示---------------------'
print '当前数据库:'+@databaseName
print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'
print '合法命名方式:'+@newNameStr
print '文件组名称:'+@fileGroupName
print 'ndf物理文件名称:'+@ndfName
print '物理文件完整路径:'+@fullPath
print '分区函数:'+@partFunName
print '分区方案:'+@schemeName
/*
--查看创建的分区函数
select * from sys.partition_functions
--查看分区函数的临界值
select * from sys.partition_range_values
--查询分区方案
select * from sys.partition_schemes
--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据
select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1
*/
GO
