SQL Server
使用資料表資料分割簡化資料庫維護
Noah Gomez
摘要:
- 建立分割資料表
- 新增及合併資料分割
- 建立及管理分割索引
下載本文程式碼: GomezPartitioning2007_03.exe (156KB)
在過去，管理包含數百萬列資料之資料表的資料庫管理員被迫要建立多重資料表。將那些資料表分割之後，資料庫管理員就必須在許多查詢執行期間將
資料表重新繫結在一起。將資料分割繫結在一起需要建立檢視或包裝函式預存程序，以了解資料存放處，然後再執行另一個預存程序，以叫用傳回資料集所需的資料分割。
雖然這些方法有效，但是很麻煩。多重資料表及其索引的管理，以及用來將資料表重新繫結在一起的方法通常會造成管理和維護問題。此外，建立多重資料表來分割資料會造成某種程度失去彈性，因為預存程序、維護作業、資料轉換服務 (DTS) 作業、應用程式碼和其他處理序必須了解資料分割的本質。為了讓您能夠新增或卸除這些準資料分割而不必變更程式碼，這些元素一般是以非動態方式建立，因此沒有效率。
SQL Server™ 2005 的 Enterprise Edition 和 Developer Edition 可讓您將單一資料表中所包含的大量資料分割成多個小型資料分割，以便更有效率地管理及維護。建立可透過單一進入點存取之資料區段的能力，可減少因為舊做法而帶來的許多管理問題。使用單一進入點 (資料表名稱或索引名稱) 可在應用程式碼中隱藏多個資料區段，並讓管理員或開發人員可以在必要時變更資料分割，而不必調整程式碼基底。
簡言之，您可以建立多個資料分割、任意移動那些資料分割、卸除舊的資料分割、甚至變更資料分割的方式，而不必調整應用程式中的程式碼。應用程式碼只需要繼續呼叫相同的基底資料表或索引名稱。同時，您可以減少個別索引包含的資料量，進而減少那些索引的維護時間，並可載入空白資料分割來增加資料載入速度。
就技術層面而言，每一個 SQL Server 2005 資料表都已分割，每一個資料表至少都有一個資料分割。SQL Server 2005 的作用是讓資料庫管理員在每一個資料表上建立其他資料分割。資料表和索引資料分割是硬式定義的資料列層級資料分割 (不允許依資料行分割)，其中允許單一進入點 (資料表名稱或索引名稱)，但應用程式碼不需要知道進入點背後的資料分割數目。資料分割可存在於基底資料表中以及與資料表相關聯的索引中。
建立分割資料表
您可以使用資料分割函數和資料分割配置來建立資料表，且可以擴充預設的單一資料分割。這些物件可讓您將資料分割成特定區段，並控制那些資料區段在儲存架構中的位置。例如，您可以根據資料保留時間或使用其他一般微分器，將資料向外擴散到多個磁碟機陣列。請注意，資料表可以根據資料表的一個資料行進行分割，且每個資料分割都必須包含無法放在其他資料分割中的資料。
資料分割函數 -- 分割資料表時，第一個決策是您要如何將資料分成不同區段。資料分割函數是用來將個別資料列對應到不同的資料分割。除了 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名資料類型或 Common Language Runtime (CLR) 使用者自訂資料類型之外，那些個別資料列都可以對應任何類型的資料行。不過，資料分割函數必須能夠將一列資料只放到一個資料表分割-它不允許一列資料同時屬於多個資料分割。
為了分割資料表，您必須在目標資料表中建立分割資料行。第一次建立資料表時，此分割資料行可存在於資料表結構描述中，以後您也可以改變資料表及新增資料行。資料行可以接受 NULL 值，但包含 NULL 值的所有資料列預設會放在資料表最左邊的資料分割。您可以在建立資料分割函數時指定將 NULL 值放在資料表最右邊的資料分割，來避免此情況發生。當您修改資料分割結構描述及新增更多的資料分割或刪除現有的資料分割時，使用左邊或右邊資料分割是一項重要的設計決策。
建立資料分割函數時，您可以選擇 LEFT 或 RIGHT 資料分割函數。LEFT 和 RIGHT 資料分割之間的差異就是界限值將放在資料分割配置中的位置。LEFT 資料分割 (這是預設值) 會將界限值包括在資料分割中，而 RIGHT 資料分割則會將界限值放在下一個資料分割中。
若要進一步了解此概念，請看看簡單的 LEFT 和 RIGHT 資料分割：
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
在第一個函數 (Left_Partition) 中，1、10 和 100 的值會分別放在第一、第二和第三個資料分割中。在第二個函數 (Right_Partition) 中，值會放在第二、第三和第四個資料分割中。
建立分割資料表時，資料分割一定要盡量平均。這可幫助您了解資料分割所需要的空間。使用 LEFT 和 RIGHT 可決定資料放置之處，進而決定資料分割大小和建立在該資料分割上的索引大小。
您可以使用 $PARTITION 函數來決定要放置資料值的資料分割編號，如下所示：
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
在第一個 SELECT 陳述式中，結果會是 2。第二個 SELECT 陳述式將傳回 3。
資料分割配置 -- 在建立函數及決定要如何分割資料之後，您必須決定個別資料分割要建立在磁碟子系統上的位置。您可以使用資料分割配置來建立此磁碟配置。資料分割配置會利用檔案群組，將每個資料分割放到磁碟子系統上的某個位置，來管理個別資料分割的磁碟空間。您可以設定資料分割配置，將所有資料分割放在單一檔案群組中、將所有資料分割放在不同的檔案群組中，或由多個資料分割共用檔案群組。後面這個方法可讓資料庫管理員在擴增磁碟 I/O 時有很大的彈性。
[圖 1] 告訴您一些方法，讓您可以指派一個檔案群組或多個檔案群組至資料分割配置中。請注意，在建立資料分割配置之前，資料分割配置所使用的檔案群組必須已存在於資料庫中。
Figure 1 將檔案群組指派至資料分割配置
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
如果您如 [圖 1] 所顯示來建立範例資料分割函數，並利用資料分割配置來建立資料表，那麼，您就可以決定個別資料列要放在新分割資料表的什麼位置。然後，在那些資料列插入分割資料表之後，您可以檢視那些資料列的分佈情形。執行上述作業的程式碼將如 [圖 2] 所示。
Figure 2 放置資料列及檢視分佈情形
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
修改分割資料表
除了審慎的事前規劃之外，有時候您需要在建立及擴展分割資料表之後調整它們。資料分割配置也許能夠依預定方式運作，但舉例而言，隨著新資料不斷地累積，您可能需要新增資料分割，或者需要一次卸除大量分割資料。還好，分割資料表和基礎分割結構允許資料表在正式上線及填入資料之後進行變更。
新增資料分割 -- 許多資料分割計劃都包括在未來新增資料分割的能力。此時間點可以是特定日期或是依據增量識別資料行中的值而定。不過，如果您未事前做此規劃，以後仍然可以在分割資料表中新增資料分割。看看在 [圖 2] 中建立的資料表。您可以在此資料表中新增資料分割來包含大於 500 的值，如下所示：
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
新增資料分割的能力提供很大的彈性。[圖 3] 告訴您如何將資料分割新增至函數的左邊。在此情況下，您需要告訴資料分割配置要在何處放置新的資料分割，因為您已經用完第一次建置資料分割配置時所建立的所有檔案群組。即使您對所有資料分割使用 PRIMARY 檔案群組，仍然必須告訴資料分割配置要對新的資料分割重複使用 PRIMARY 檔案群組。
Figure 3 將資料分割新增至函數的左邊
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
合併兩個資料分割 -- SQL Server 可讓您在保留資料時，從資料表卸除單一資料分割。這可用來將較舊的使用中資料合併到保存的資料，或減少您擁有的資料分割數目，以簡化分割資料表的管理。您也可以利用此方式來合併資料分割，將一個檔案群組的資料移到另一個檔案群組，以釋放特定磁碟機陣列上的磁碟空間。[圖 4] 中的程式碼告訴您如何將一個資料分割中的資料移到相同檔案群組上的另一個資料分割。
Figure 4 將資料從一個資料分割移到另一個資料分割
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
將單一分割資料表移到分割資料表 -- 在例行載入工作期間，大量資料通常必須載入至資料庫，然後在移到實際資料表之前必須加以修改或彙總。SQL Server 2005 資料分割可讓您將單一分割資料表移到含有多個資料分割的資料表中。這表示您可以將資料載入單一載入資料表中、修改該資料，然後將整個資料表移到現有的資料表中，而不必一一移動個別資料列。這一層資料分割不包含改變基礎資料分割結構。它包含修改分割資料表。[圖 5] 中的程式碼告訴您如何達成此目的。
Figure 5 將整個資料表移到現有的資料表
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
將一個資料表的資料分割移到另一個資料表 -- 一般管理工作是將較舊的資料移到個別的保存資料表。保存處理序通常包含一系列的陳述式，而這會在交易記錄檔中造成其他資源的負荷。不過，將資料分割的擁有權從一個資料表切換到另一個資料表，是保存大量資料而不造成交易記錄檔負擔的簡單方式。此功能可讓資料庫管理員將較舊資料的區段，從使用中資料表移到保存的資料表。但由於資料並未實際移動，它需要的時間量將遠少於移動個別資料列所花費的時間。[圖 6] 會告訴您如何進行。
Figure 6 將較舊的資料移到保存的資料表
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
使用單一資料分割來建立新資料表 -- 您可以從現有的分割資料表中將單一資料分割移到空白非分割資料表中。這麼做，資料庫管理員就可以在單一資料分割執行索引維護，或輕易卸除大量資料，而不必記錄刪除處理序。[圖 7] 中的範例顯示如何將資料分割移到空白資料表中，然後使用新資料表來卸除資料。
Figure 7 移動及卸除資料
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
分割索引
有分割資料表資料的能力就有建立分割索引的能力。這可讓資料庫管理員根據分割的資料而非整個資料表的資料來設計索引結構。建立分割索引會使分割索引上產生個別 B 型樹狀目錄。索引分割具有產生小型索引的效果，使儲存引擎在資料修改、新增和刪除期間更容易維護它們。這些小型索引也可以由資料庫管理員個別維護，使大型資料集有更佳的索引維護。
建立分割索引 -- 建立分割索引時，您可以建立對齊索引或非對齊索引。若是對齊索引，您可以建立與分割資料有直接關係的索引 (若是非對齊索引，您可以選擇不同的資料分割結構描述)。
對齊是慣用方法，如果您建立分割資料表，然後建立索引而未指定不同分割配置，就會自動執行對齊。使用對齊索引讓您有彈性在資料表上建立其他資料分割，並讓您有能力將資料分割的擁有權切換到另一個資料表。這些功能通常是資料庫管理員首先建立分割資料表的原因，而只要使用索引資料表的分割結構描述，就可能達成資料分割目標。
當索引中的資料與資料表中的資料不對齊時，您可以對資料表建立索引。如果資料是在分割資料表中，這樣做可讓您以不同方式加入資料 (查詢最佳化工具可以有效率地結合分割的資料與其他分割資料)。另外，您也可以對非分割資料表執行此動作，讓您建立分割索引 (針對單一分割資料表)，以便您簡化索引維護。
[圖 8] 中的程式碼將對分割資料表建立分割式非叢集索引。非叢集索引將與資料表對齊，並利用資料表的分割資料行做為非叢集索引鍵。
Figure 8 對分割資料表建立分割式非叢集索引
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
[圖 9] 中的程式碼將對分割資料表建立非對齊的非叢集索引。這個非叢集索引將對其索引鍵使用不同資料行，這可用於其他分割資料表的自動分頁結合。
Figure 9 對分割資料表建立非對齊的非叢集索引
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
維護分割索引 -- 在過去，針對包含數百萬、甚至數十億資料列的大型資料表執行索引維護，通常會花費超過資料庫管理員擁有的時間。此維護工作常未執行，因為重新建置索引時會鎖定資料。有了 SQL Server 2005，資料庫管理員可以線上執行索引維護，而不必長期鎖定基礎資料表。但即使此種方式 (當使用者在存取資料時讓您能夠執行索引維護) 仍然會因為資源用量而使系統速度變慢。比較好的方式是將索引分割成較小的區段，然後對那些小型資料分割執行索引維護。例如，若要對一個索引資料分割執行索引維護，您只要將下列程式碼片段附加到 [圖 8] 所顯示的程式碼結尾下方即可。
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
請注意，對單一索引資料分割的索引維護必須離線執行，並可能會在索引維護期間造成資料表鎖定。為避免發生此情形，您可以將單一資料分割移到個別資料分割，執行索引維護，然後將資料分割再移回到主資料表。此處理序會造成一些效能問題，因為資料分割會移回到資料表，且叢集索引會更新，但比起鎖定整個資料表，這樣做的問題較小，且需要的系統資源也較少。
結論
如您所見，SQL Server 2005 資料表分割對大型資料表中資料的儲存和維護提供更佳的彈性，而不必重做應用程式碼或 SQL Server 處理序。有了這些功能，SQL Server 證明自己是對企業級、重要資料庫的高效能平台。
Noah Gomez 是 Verizon 的資深 SQL Server Development DBA，專精 VLDB 和大型應用程式。他是 Professional Association for SQL Server (PASS) 的成員，曾經是規模大到數兆位元組 VLDB 之 Verizon DBA 小組的一員，並在 2003 年贏得 Winter Corp. Top Ten Grand Prize 大獎。
© 2008 Microsoft Corporation and CMP Media, LLC. 保留所有權利；未經允許，嚴禁部分或全部複製.