建立分區

【IT專家網獨家】你是否在千方百計最佳化SQL Server 資料庫的效能?如果你的資料庫中含有大量的表格，把這些表格分區放入獨立的檔案組可能會讓你受益匪淺。SQL Server 2005引入的表分區技術，讓使用者能夠把資料分散存放到不同的物理磁碟中，提高這些磁碟的平行處理效能以最佳化查詢效能。

　　SQL Server資料庫表分區操作過程由三個步驟組成：

　　1. 建立分區函數

　　2. 建立分區架構

　　3. 對錶進行分區

　　下面將對每個步驟進行詳細介紹。

　　步驟一：建立一個分區函數

　　此分區函數用於定義你希望SQL Server如何對資料進行分區的參數值([u]how[/u])。這個操作並不涉及任何錶格，只是單純的定義了一項技術來分割資料。

　　我們可以通過指定每個分區的邊界條件來定義分區。例如，假定我們有一份Customers表，其中包含了關於所有客戶的資訊，以一一對應的客戶編碼(從1到1,000,000)來區分。我們將通過以下的分區函數把這個表分為四個大小相同的分區：　　

 

CREATE PARTITION FUNCTION customer_partfunc (int) 　　AS RANGE RIGHT 　　FOR VALUES (250000, 500000, 750000)

　　這些邊界值定義了四個分區。第一個分區包括所有值小於250,000的資料，第二個分區包括值在250,000到49,999之間的資料。第三個分區包括值在500,000到7499,999之間的資料。所有值大於或等於750,000的資料被歸入第四個分區。

　　請注意，這裡調用的"RANGE RIGHT"語句表明每個分區邊界值是右界。類似的，如果使用"RANGE LEFT"語句，則上述第一個分區應該包括所有值小於或等於250,000的資料，第二個分區的資料值在250,001到500,000之間，以此類推。

　　步驟二：建立一個分區架構

　　一旦給出描述如何分割資料的分區函數，接著就要建立一個分區架構，用來定義分區位置([u]where[/u])。建立過程非常直截了當，只要將分區串連到指定的檔案組就行了。例如，如果有四個檔案組，組名從"fg1"到"fg4"，那麼以下的分區架構就能達到想要的效果：　　

 

CREATE PARTITION SCHEME customer_partscheme 　　AS PARTITION customer_partfunc 　　TO (fg1, fg2, fg3, fg4)

　　注意，這裡將一個分區函數串連到了該分區架構，但並沒有將分區架構串連到任何資料表。這就是可複用性起作用的地方了。無論有多少資料庫表，我們都可以使用該分區架構(或僅僅是分區函數)。

　　步驟三：對一個表進行分區

　　定義好一個分區架構後，就可以著手建立一個分區表了。這是整個分區操作過程中最簡單的一個步驟。只需要在表建立指令中添加一個"ON"語句，用來指定分區架構以及應用該架構的表列。因為分區架構已經識別了分區函數，所以不需要再指定分區函數了。

　　例如，使用以上的分區架構建立一個客戶表，可以調用以下的Transact-SQL指令：　　

 

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int) 　　ON customer_partscheme (CustomerNumber)

　　關於SQL Server的表資料分割函數，你知道上述的相關知識就足夠了。記住!編寫能夠用於多個表的一般的分區函數和分區架構就能夠大大提高可複用性。

=======================================第二遍==================================================

--建立分區表過程一共分為三步:建立分區函數、建立資料分割配置、建立分區表

 

/*本實驗涉及兩個表:transactionhistory、transactionhistoryarchive，資料從adventureworks導過來，
下面要將這兩張表分別建成分區表*/
--建立分區表transactionhistory
--建立分區函數
use wjz
go
create partition function transactionhistorypf1(datetime)
as range right for values(
'2003-9-1','2003-10-1','2003-11-1','2003-12-1','2004-1-1',
'2004-2-1','2004-3-1','2004-4-1','2004-5-1','2004-6-1',
'2004-7-1','2004-8-1'
);
go

--建立資料分割配置
create partition scheme transactionhistoryps1
as partition transactionhistorypf1 to
([primary],wjz2,wjz3,wjz4,wjz5,wjz6,wjz7,wjz8,wjz9,wjz10,wjz11,wjz12,wjz13,wjz14)
go

--建立分區表
CREATE TABLE [TransactionHistory](
[TransactionID] [int] IDENTITY(100000,1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID]   DEFAULT ((0)),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate]   DEFAULT (getdate()),
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate]   DEFAULT (getdate()),

) ON transactionhistoryps1(TransactionDate);
go

--建立分區表transactionhistoryarchive
--建立分區函數
create partition function transactionhistoryarchivepf1(datetime)
as range right for values( '2003-9-1','2003-10-1')
go

--建立資料分割配置
create partition scheme transactionhistoryarchiveps1
as partition transactionhistoryarchivepf1 to(
[primary],wjz2,wjz3
);
go

--建立分區表
CREATE TABLE [TransactionHistoryArchive](
[TransactionID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ReferenceOrderLineID]   DEFAULT ((0)),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate]   DEFAULT (getdate()),
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate]   DEFAULT (getdate()),

) ON transactionhistoryarchiveps1(transactiondate);
go

--Load data use SSIS

--分區表建立完畢

--管理分區表
--將transactionhistory的第2個分區移動至transactionhistoryarchive的第2個分區
--注意，兩張表的第2個分區剛好位於同一個檔案組wjz2中
alter table transactionhistory
switch partition 2 to
transactionhistoryarchive
partition 2
go
--驗證一下資料已經在一秒鐘之內轉移到新表裡了
select * from transactionhistoryarchive

--為transactionhistory表在右端新增一個分區
--注意，因為我建立資料分割配置時多寫了一個檔案組，因此那個多餘的檔案組就成了next used，
--否則要先修改資料分割配置來增加新的可用檔案組
alter partition function transactionhistorypf1()
split range ('2004-9-1')

--將transactionhistory表左端的兩個空分區合而為一
alter partition function transactionhistorypf1()
merge range('2003-9-1')
go

--為transactionhistoryarchive表在右端新增一個分區，做好下次轉移準備，注意現在要先修改資料分割配置
alter partition scheme transactionhistoryarchiveps1
next used wjz4
go
alter partition function transactionhistoryarchivepf1()
split range('2003-11-1')
go

--將transactionhistoryarchive表左端的兩個分區合并（這個可選，至少是好是壞，目前還沒想好）
alter partition function transactionhistoryarchivepf1()
merge range('2003-9-1')

/*刪除用的
drop table transactionhistoryarchive
go
drop partition scheme transactionhistoryarchiveps1
go
drop partition function transactionhistoryarchivepf1
go

drop table transactionhistory
go
drop partition scheme transactionhistoryps1
go
drop partition function transactionhistorypf1
go*/

--以上情況可寫成job進行迴圈