SQL Server: 分区实践
SqlServer
2020-01-15
453
0
一、手动创建分区
--创建分区函数 USE [guanggao]; CREATE PARTITION FUNCTION RangeTime (DATETIME) AS RANGE LEFT FOR VALUES ('2016-03-27','2016-04-03'); --增加FileGroup USE [guanggao]; GO ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160327]; GO ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160403]; GO ALTER DATABASE [guanggao] ADD FILEGROUP [FileGroup20160410]; GO --增加FileName USE [guanggao]; GO ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160327', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160327.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160327]; GO ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160403', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160403.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160403]; GO ALTER DATABASE [guanggao] ADD FILE ( NAME = N'File20160410', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\File20160410.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroup20160410]; GO --创建分区Schema CREATE PARTITION SCHEME RangeSchema_CreateTime AS PARTITION RangeTime TO ([FileGroup20160327],[FileGroup20160403],[FileGroup20160410]); --创建分区表 CREATE TABLE [dbo].[bihell_partition] ( ....... ) ON RangeSchema_CreateTime(create_date); --插入数据 INSERT INTO dbo.bihell_partition SELECT * FROM dbo.bihell; --统计每个分区的数据量 SELECT $partition.RangeTime(create_date) AS number , COUNT(*) AS rcount FROM [bihell_partition] GROUP BY $partition.RangeTime(create_date);
二、自动创建分区并添加到分区函数
DECLARE @maxValue DATETIME , @secondMaxValue DATETIME , @differ INT , @fileGroupName VARCHAR(200) , @fileNamePath VARCHAR(200) , @fileName VARCHAR(200) , @sql NVARCHAR(1000); SET @fileGroupName = 'FileGroup' + REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)), '-', ''); PRINT @fileGroupName; SET @sql = 'ALTER DATABASE [guanggao] ADD FILEGROUP ' + @fileGroupName; PRINT @sql; --EXEC(@sql) SET @fileNamePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\' + REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)), '-', '') + '.NDF'; SET @fileName = N'File' + REPLACE(CAST(CAST(DATEADD(d, 6, CURRENT_TIMESTAMP) AS DATE) AS VARCHAR(10)), '-', ''); SET @sql = 'ALTER DATABASE [guanggao] ADD FILE (NAME=''' + @fileName + ''',FILENAME=N''' + @fileNamePath + ''') TO FILEGROUP' + ' ' + @fileGroupName; PRINT @sql; --EXEC(@sql) --修改分区方案,用一个新的文件组用于存放下一新增的数据 SET @sql = 'ALTER PARTITION SCHEME [RangeSchema_CreateTime] NEXT USED' + ' ' + @fileGroupName; PRINT @sql; --EXEC(@sql); --分区架构 SELECT @maxValue = CAST (MAX(value) AS DATETIME) FROM sys.partition_range_values PRV WHERE PRV.function_id = 65538; PRINT @maxValue; SELECT @secondMaxValue = CAST (MIN(value) AS DATETIME) FROM ( SELECT TOP 2 * FROM sys.partition_range_values WHERE function_id = 65538 ORDER BY value DESC ) PRV; PRINT @secondMaxValue; SET @differ = DATEDIFF(d, @secondMaxValue, @maxValue); PRINT @differ; ALTER PARTITION FUNCTION RangeTime() --分区函数 SPLIT RANGE (DATEADD(d,@differ,@maxValue));
三、其他参考文档
BIWORK 分区表滑动窗口阅读与实践笔记
BIWORK 分区表阅读与实践笔记
sql server 小记——分区表(上)
SQL Server 解读【已分区索引的特殊指导原则】(1)- 索引对齐
SQL Server锁分区特性引发死锁解析
SqlServer 自动化分区方案
再说一下表分区
数据库优化实践【文件、文件组、分区篇】
深入浅出SQL Server 2008 分区函数和分区表
0条评论