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
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