概述

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

一、創(chuàng)建測試數(shù)據(jù)

—-01創(chuàng)建文件組
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創(chuàng)建分區(qū)函數(shù)
USE [chenmh]
GO
CREATE PARTITION FUNCTION [Pt_Range](BIGINT) AS RANGE RIGHT FOR VALUES (1000000, 2000000, 3000000)
GO

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

—04創(chuàng)建表,指定的分區(qū)列的數(shù)據(jù)類型一定要和分區(qū)函數(shù)指定的列類型一致。
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)

—–創(chuàng)建歸檔分區(qū)表
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)

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

END