SQL Server Partition Table T-SQL 分割資料表實作步驟


1 新增空白資料庫
USE [master]
GO
CREATE DATABASE [PartitionTableLab]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'PartitionTableLab', FILENAME = N'F:\SQL_DATA\PartitionTableLab.mdf'  , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PartitionTableLab_log', FILENAME =  N'F:\SQL_DATA\PartitionTableLab_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB ,  FILEGROWTH = 1024KB )
GO



2 以年度建立 Partition Function 與 Partition Schema
USE [master]
GO

ALTER DATABASE [PartitionTableLab] ADD FILEGROUP [FG_2012];
ALTER DATABASE [PartitionTableLab] ADD FILEGROUP [FG_2013];
ALTER DATABASE [PartitionTableLab] ADD FILEGROUP [FG_2014];
GO

ALTER DATABASE [PartitionTableLab] ADD FILE ( NAME = N'HIS_2012', FILENAME =  N'F:\SQL_DATA\PartitionTableLab_his_2012.ndf'
, SIZE = 1024KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG_2012]
GO
ALTER DATABASE [PartitionTableLab] ADD FILE ( NAME = N'HIS_2013', FILENAME =  N'F:\SQL_DATA\PartitionTableLab_his_2013.ndf'
, SIZE = 1024KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG_2013]
GO
ALTER DATABASE [PartitionTableLab] ADD FILE ( NAME = N'HIS_2014', FILENAME =  N'F:\SQL_DATA\PartitionTableLab_his_2014.ndf'
, SIZE = 1024KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG_2014]
GO
USE [PartitionTableLab];
GO

/*
DROP TABLE Orders;
DROP PARTITION SCHEME P_Scheme;
DROP PARTITION FUNCTION P_Func;
*/
/*建立 Partition Function 與 Partition Schema */
CREATE PARTITION FUNCTION P_Func(datetime)
AS RANGE RIGHT FOR VALUES
       (convert(date, '2013-01-01')
       ,convert(date, '2014-01-01')
);
GO

CREATE PARTITION SCHEME P_Scheme
AS PARTITION P_Func TO (FG_2012, FG_2013, FG_2014);
GO



3. 建立資料表並填入資料至其中一個年度, 驗證該年度的分割資料表實體檔案有增加
/*新增資料並儲存於 Partition Schema */
CREATE TABLE Orders(
OrderID int NOT NULL,
OrderDate datetime NOT NULL,
OrderRemark nchar(1024) NULL,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED 
(
OrderID ASC,
OrderDate ASC
) ON P_Scheme(OrderDate)
) ON [PRIMARY];
GO

/*查詢 Partition 有幾筆資料 */
SELECT index_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders')
ORDER BY index_id, partition_number;
GO

/*新增多筆資料在單一年度中*/
DECLARE @OrderRemark char(1024)
DECLARE @X1 int = 1, @X2 int = 1;
WHILE (@X1<=@X2)
BEGIN
       DECLARE @run int = 1, @end int = 330
       WHILE (@run<=@end)
       BEGIN
              SET @OrderRemark =  convert(varchar(36),NEWID())+'|'+convert(varchar(36),NEWID())+'|'+convert(varchar(36),NEWID());
              insert into Orders(OrderID, OrderDate, OrderRemark)
              values((@X1*@end+@run), DATEADD(day, @run, convert(date,  '2013-01-01')), @OrderRemark)
              
              SET @run = @run + 1;
       END
       SET @X1 = @X1 + 1;
END
GO

/*新增一個年度的儲存檔案 */
ALTER DATABASE [PartitionTableLab] ADD FILEGROUP [FG_2015];
GO
ALTER DATABASE [PartitionTableLab] ADD FILE ( NAME = N'HIS_2015', FILENAME =  N'F:\SQL_DATA\PartitionTableLab_his_2015.ndf'
, SIZE = 1024KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG_2015];
GO
ALTER PARTITION SCHEME P_Scheme
NEXT USED FG_2015;
GO
ALTER PARTITION FUNCTION [P_Func]()
SPLIT  RANGE (convert(date, '2015-01-01'));
GO


4. 回復重新建立分割資料表

從各個分割資料表中移回主要資料表
ALTER TABLE Orders DROP CONSTRAINT PK_Orders
GO

ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY  KEY CLUSTERED
(
       OrderID ASC,
       OrderDate ASC
) ON [PRIMARY]
GO

/*查詢 Partition 有幾筆資料 */
SELECT index_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders')
ORDER BY index_id, partition_number;
GO



從主要資料表移置個分割資料表
ALTER TABLE Orders DROP CONSTRAINT PK_Orders
GO

ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY  KEY CLUSTERED
(
       OrderID ASC,
       OrderDate ASC
) ON P_Scheme(OrderDate)
GO

/*查詢 Partition 有幾筆資料 */
SELECT index_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Orders')
ORDER BY index_id, partition_number;
GO