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
) 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
/*新增多筆資料在單一年度中*/
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

Evernote helps you remember everything and get organized effortlessly. Download Evernote.

Popular posts from this blog

Google Map 多點路線規劃

解決瀏覽器無法下載檔案