WizardWu 編程網

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