SqlServer 表分区

参考 https://www.cnblogs.com/hhhh2010/p/10429002.html

参考 https://www.cnblogs.com/xiaomengshan/p/11139299.html

 

用 ssms 图形界面操作分区后，可能会需要调整分区大小及数量

 

下面是删除一个分区，分区删除后，分区的数据自动移到对应存在的分区里，（分区方案会自动执行删除对应的文件组不用写脚本）

  --删除一个分区
  ALTER PARTITION FUNCTION  [Role_FQ_Fun]() MERGE RANGE (20)

 

下面是添加一个分区，

  --修改分区方案，用一个新的文件组用于存放下一新增的数据
  ALTER PARTITION SCHEME  [Role_FQ_Scheme] NEXT USED [FileGroup4]
  --修改分区函数，添加下一个边界值
  ALTER PARTITION FUNCTION [Role_FQ_Fun]() SPLIT   RANGE ( 18)

 

查看分区信息

--查看分区
SELECT  SCHEMA_NAME(so.schema_id) AS schema_name ,
OBJECT_NAME(p.object_id) AS object_name ,
 p.partition_number ,
 p.data_compression_desc ,
 dbps.row_count ,
 dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,
 si.index_id ,
 CASE WHEN si.index_id = 0 THEN '(heap!)'
         ELSE si.name
 END AS index_name ,
 si.is_unique ,
 si.data_space_id ,
 mappedto.name AS mapped_to_name ,
 mappedto.type_desc AS mapped_to_type_desc ,
 partitionds.name AS partition_filegroup ,
 pf.name AS pf_name ,
 pf.type_desc AS pf_type_desc ,
 pf.fanout AS pf_fanout ,
 pf.boundary_value_on_right ,
 ps.name AS partition_scheme_name ,
 rv.value AS range_value            FROM    sys.partitions p            JOIN    sys.objects so
 ON p.object_id = so.object_id
     AND so.is_ms_shipped = 0            LEFT JOIN sys.dm_db_partition_stats AS dbps
 ON p.object_id = dbps.object_id
     AND p.partition_id = dbps.partition_id            JOIN    sys.indexes si
 ON p.object_id = si.object_id
     AND p.index_id = si.index_id            LEFT JOIN sys.data_spaces mappedto
 ON si.data_space_id = mappedto.data_space_id            LEFT JOIN sys.destination_data_spaces dds
 ON si.data_space_id = dds.partition_scheme_id
     AND p.partition_number = dds.destination_id            LEFT JOIN sys.data_spaces partitionds
 ON dds.data_space_id = partitionds.data_space_id            LEFT JOIN sys.partition_schemes AS ps
 ON dds.partition_scheme_id = ps.data_space_id            LEFT JOIN sys.partition_functions AS pf
 ON ps.function_id = pf.function_id            LEFT JOIN sys.partition_range_values AS rv
 ON pf.function_id = rv.function_id
     AND dds.destination_id = CASE pf.boundary_value_on_right

              WHEN 0 THEN rv.boundary_id

              ELSE rv.boundary_id + 1

          END

===========================================

动态表分区

通过 SqlServer 代理 作业 定时执行 存储过程 动态根据 主键 id 段 划分 表分区

 

创建一个存储过程

ssms 右键创建就好，然后把下面的逻辑加进去

执行划分的存储过程 逻辑内容

DECLARE 
	@dataBaseName VARCHAR(20),    --数据库名
	@rang int,		--每个边界数量
	@fileName VARCHAR(20), 
	@filePath VARCHAR(100),
	@index int,
	@fileGroupName VARCHAR(20),
	@schemeName VARCHAR(20),    --分区方案名
	@partFunctionName VARCHAR(20),    --分区函数名
	@limit VARCHAR(50),    --分区界限
	@while_i int

	set @rang=5
	SET @dataBaseName='School'
	SET @schemeName='Role_FQ_Scheme'
	SET @partFunctionName='Role_FQ_Fun'
	
--计算索引序号
select @index=max(id)/@rang from School.[dbo].[Role]



	set @while_i=@index


while @while_i>0
begin		--while  start


	set @fileName='FileGroup'+ CAST(@while_i as varchar ) 
	set @fileGroupName=@fileName
	set @filePath='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\'+@fileName+'.ndf'
	set @limit=CAST(@while_i*@rang as varchar )

--判断文件组 (语句要指明需要操作的数据库)
if exists(select * from School.sys.filegroups where name=@fileGroupName)
    begin
        print '文件组存在,不需添加'
		break;  --推出循环
    end
else
    begin
        exec('ALTER DATABASE '+@dataBaseName+' ADD FILEGROUP ['+@fileGroupName+']')
        print '新增文件组'+@fileGroupName
    end

--判断文件
if exists(select * from School.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath))
    begin
        print 'ndf文件存在,不需添加';
    end
else
    begin
        exec('ALTER DATABASE '+@dataBaseName+' ADD FILE(NAME ='''+@fileName+''',FILENAME = '''+@filePath+''')TO FILEGROUP ['+@fileGroupName+']')
        print '添加文件'+@fileName+'至文件组'+@fileGroupName
    end
--修改分区方案
if exists(select * from sys.partition_schemes where name=@schemeName)
    begin
        exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
        print '修改分区方案，指定下一分区的文件组'
    end
else
    begin
        print '分区方案不存在'
    end

--修改分区函数
if exists(select function_id from sys.partition_functions where name=@partFunctionName)
    begin
        if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name=@schemeName) and value=CAST( @limit as int))
            begin
                print '界限已存在'
            end
        else
            begin
                exec('alter partition function '+@partFunctionName+'() split range('''+@limit+''')')
                print '修改分区函数，添加划分界限为：'+@limit
            end
    end
else
    begin
        print '分区函数不存在'
    end




	set @while_i=@while_i-1;
end		--while  end

创建 SqlServer 的计划任务

1、打开SQL Server Management Studio,SQL Server代理 --作业--点右键--新建作业。
2、写上定时执行存储过程名称（任意），点击确定保存。
3、点击常规下面的步骤，按箭头指示，新建步骤，选择存储过程所用的数据库，录入需要执行的命令和参数，点击确定保存。
4、新建计划，进计划属性设置窗口，设置计划自动定时执行的时间。确定保存即可。