概述

大家應該都知道在很多業務場景下我們需要對一些記錄量比較大的表進行分區，同時為了保證性能需要將一些舊的數據進行歸檔。在分區表很多的情況下如果每一次歸檔都需要人工干預的話工程量是比較大的而且也容易發生紕漏。接下來分享一個自己編寫的自動歸檔分區數據的腳本，原理是分區表和歸檔表使用相同的分區方案，循環利用當前的文件組，話不多說了，來一起看看詳細的介紹吧。

一、創建測試數據

—-01創建文件組
USE [master]
GO
ALTER DATABASE [chenmh] ADD FILEGROUP [Group1]
GO
ALTER DATABASE [chenmh] ADD FILEGROUP [Group2]
GO
ALTER DATABASE [chenmh] ADD FILEGROUP [Group3]
GO
ALTER DATABASE [chenmh] ADD FILEGROUP [Group4]
GO
USE [master]
GO
ALTER DATABASE [chenmh] ADD FILE ( NAME = N’datafile1′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile1.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group1]
GO
ALTER DATABASE [chenmh] ADD FILE ( NAME = N’datafile2′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile2.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group2]
GO
ALTER DATABASE [chenmh] ADD FILE ( NAME = N’datafile3′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile3.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group3]
GO
ALTER DATABASE [chenmh] ADD FILE ( NAME = N’datafile4′, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAdatafile4.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group4]
GO

—-02創建分區函數
USE [chenmh]
GO
CREATE PARTITION FUNCTION [Pt_Range](BIGINT) AS RANGE RIGHT FOR VALUES (1000000, 2000000, 3000000)
GO

—-03創建分區方案,分區方案對應的文件組數是分區函數指定的數量+1
CREATE PARTITION SCHEME Ps_Range
AS PARTITION Pt_Range
TO (Group1, Group2, Group3, Group4);

—04創建表,指定的分區列的數據類型一定要和分區函數指定的列類型一致。
CREATE TABLE [dbo].[News](
[id] [bigint] NOT NULL,
[status] [int] NULL,
CONSTRAINT [PK_News] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_Range](id)
) ON [Ps_Range](id)

—–創建歸檔分區表
CREATE TABLE [dbo].[NewsArchived](
[id] [bigint] NOT NULL,
[status] [int] NULL,
CONSTRAINT [PK_NewsArchived] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_Range](id)
) ON [Ps_Range](id)

—-插入測試數據
DECLARE @id INT
SET @id=1
WHILE @id<5001000
BEGIN
INSERT INTO News VALUES(@id,@id%2)
SET @id=@id+1

END