alter database NORTHWND add filegroup FileGroup1;

alter database NORTHWND add filegroup FileGroup2;

alter database NORTHWND add filegroup FileGroup3;

alter database NORTHWND add file(name=FG11, filename='C:\test\FG11.ndf') to filegroup FileGroup1;

alter database NORTHWND add file(name=FG21, filename='C:\test\FG21.ndf', size=10MB),

                                (name=FG22, filename='C:\test\FG22.ndf', size=10MB) to filegroup FileGroup2;

alter database NORTHWND add file(name=FG31, filename='C:\test\FG31.ndf') to filegroup FileGroup3;

--依「訂單日期」欄位，自動將 record 儲存至不同的 FileGroup、實體 file

create partition function partitionFunction_OrderDate(datetime) as range RIGHT for values('1997/01/01','1998/01/01','1999/01/01');

--1997/01/01 之前的舊 record，存至 FileGroup1 (FG11.ndf)

--1997/01/01~1998/12/31 的 record，存至 FileGroup2 (FG21.ndf、FG22.ndf)

--1999/01/01 及之後的 record，存至 FileGroup3 (FG31.ndf)

create partition scheme partitionScheme_OrderDate as partition partitionFunction_OrderDate to (FileGroup1, FileGroup2, FileGroup2, FileGroup3);

create table Orders2(

OrderID int not null,

OrderDate datetime not null default getdate(),

CustID varchar(6),

EmpID varchar(6)) ON partitionScheme_OrderDate(OrderDate)

--從 Orders 資料表，匯入測試用資料 (都是早期年份，2000 年之前的記錄)

INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) SELECT OrderID, OrderDate, CustomerID, EmployeeID FROM Orders

--新增幾筆 OrderDate 較新的資料，供測試用 (2014 年份)

INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12001, getdate(), '2014/7', '2014/7')

INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12002, getdate(), '2014/8', '2014/8')

INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12003, getdate(), '2014/9', '2014/9')

--檢查 Orders2 資料表中的資料，其水平切割、分組情形，或者由 sys.partitions 檢視表中查詢每個 partition 的資料筆數

SELECT index_id, partition_number, rows FROM sys.partitions

WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number;

--測試日後新增 partition 時 (模擬企業在每年進入新的年份，都要持續新建 partition，供搬移歷史舊資料)

alter database NORTHWND add filegroup FileGroup4;

alter database NORTHWND add file(name=FG41, filename='C:\test\FG41.ndf') to filegroup FileGroup4;

--split 新 partition (以 2000/01/01 年為分界點)

alter partition scheme partitionScheme_OrderDate NEXT USED [FileGroup4];

alter partition function partitionFunction_OrderDate() SPLIT range ('2000/01/01');

--create partition scheme partitionScheme_OrderDate as partition partitionFunction_OrderDate to (FileGroup1, FileGroup2, FileGroup2, FileGroup3);

--split 新 partition 之後，既有的記錄 (2000/01/01 之後的記錄) 會自動搬移 (視 OrderDate 欄位的年份自動對應)，不用資訊人員手動搬移

SELECT index_id, partition_number, rows FROM sys.partitions

WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number;

--日後再 insert 的記錄 (2000/01/01 之後的記錄)，會自動改寫至 FileGroup4

INSERT INTO Orders2(OrderID, OrderDate, CustID, EmpID) VALUES(12005, getdate(), '2014/9', '2014/9')

--SELECT index_id, partition_number, rows FROM sys.partitions

--WHERE object_id = OBJECT_ID('Orders2') ORDER BY index_id, partition_number;

--SELECT * FROM Orders2 ORDER BY OrderDate desc