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 @[email protected]


while @while_i>0
begin		--while  start


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

--判斷檔案組 (語句要指明需要操作的資料庫)
if exists(select * from School.sys.filegroups where [email protected])
    begin
        print '檔案組存在,不需添加'
		break;  --推出循環
    end
else
    begin
        exec('ALTER DATABASE '[email protected]+' ADD FILEGROUP ['[email protected]+']')
        print '新增檔案組'[email protected]
    end

--判斷檔案
if exists(select * from School.sys.database_files where [state]=0 and ([email protected] or [email protected]))
    begin
        print 'ndf檔案存在,不需添加';
    end
else
    begin
        exec('ALTER DATABASE '[email protected]+' ADD FILE(NAME ='''[email protected]+''',FILENAME = '''[email protected]+''')TO FILEGROUP ['[email protected]+']')
        print '添加檔案'[email protected]+'至檔案組'[email protected]
    end
--修改分區方案
if exists(select * from sys.partition_schemes where [email protected])
    begin
        exec('alter partition scheme '[email protected]+' next used ['[email protected]+']')
        print '修改分區方案，指定下一分區的檔案組'
    end
else
    begin
        print '分區方案不存在'
    end

--修改分區函數
if exists(select function_id from sys.partition_functions where [email protected])
    begin
        if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where [email protected]) and value=CAST( @limit as int))
            begin
                print '界限已存在'
            end
        else
            begin
                exec('alter partition function '[email protected]+'() split range('''[email protected]+''')')
                print '修改分區函數，添加劃分界限為：'[email protected]
            end
    end
else
    begin
        print '分區函數不存在'
    end




	set @[email protected]_i-1;
end		--while  end
           

建立 SqlServer 的計劃任務

1、打開SQL Server Management Studio,SQL Server代理 --作業--點右鍵--建立作業。

2、寫上定時執行存儲過程名稱（任意），點選确定儲存。

3、點選正常下面的步驟，按箭頭訓示，建立步驟，選擇存儲過程所用的資料庫，錄入需要執行的指令和參數，點選确定儲存。

4、建立計劃，進計劃屬性設定視窗，設定計劃自動定時執行的時間。确定儲存即可。