概述

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

一、創(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ū)方案對應(yīng)的文件組數(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