CREATE PARTITION FUNCTION (Transact-SQL)

發行項
09/10/2014

在目前資料庫中建立一個函數，根據指定資料行的各個值，將資料表或索引的資料列對應到資料分割中。使用 CREATE PARTITION FUNCTION 是建立資料分割資料表或索引的第一步。 SQL Server 2012 中，一個資料表或索引最多可以有 15,000 個資料分割。

主題連結圖示 Transact-SQL 語法慣例

語法

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

引數

partition_function_name
這是資料分割函數的名稱。資料分割函數名稱在資料庫內必須是唯一的，且必須符合識別碼的規則。
input_parameter_type
這是資料分割所用之資料行的資料類型。除了 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名資料類型或 CLR 使用者自訂資料類型，所有資料類型都能有效用在資料分割資料行上。

實際資料行稱為「資料分割資料行」，指定在 CREATE TABLE 或 CREATE INDEX 陳述式中。
boundary_value
指定使用 partition_function_name 之資料分割資料表或索引的每份資料分割的界限值。如果 boundary_value 是空的，資料分割函數會將使用 partition_function_name 的整份資料表或索引對應到單一資料分割。只能使用 CREATE TABLE 或 CREATE INDEX 陳述式中所指定的一個資料分割資料行。

boundary_value 是一個可以參考變數的常數運算式。其中包括使用者自訂類型變數，或函數和使用者自訂函數。它不能參考 Transact-SQL 運算式。 boundary_value 必須符合 input_parameter_type 所提供的資料類型，或可以隱含地轉換成這個資料類型，且在隱含地轉換期間，不能因為值的大小和小數位數不符合對應 input_parameter_type 的大小和小數位數而被截斷。

[!附註]

如果 boundary_value 是由 datetime 或 smalldatetime 常值所組成，這些常值將在假設工作階段語言為 us_english 條件下進行評估。這個行為已被取代。為了確定使用所有工作階段語言時資料分割函數定義的行為都可如所預期，建議您使用所有語言設定都會解譯成相同內容的常數，例如 yyyymmdd 格式；或是將常值明確轉換成特定樣式。若要判斷伺服器的工作階段語言，請執行 SELECT @@LANGUAGE。
...n
指定 boundary_value 所提供值的數目，但不可超過 14,999。所建立的資料分割數目等於 n + 1。這些值不必依照順序列出。如果值沒有排序，Database Engine 會將它們排序、建立函數，以及傳回未依序提供值的警告。如果 n 包括任何重複的值，Database Engine 會傳回錯誤。
LEFT | RIGHT
指定當 Database Engine 是按遞增順序由左至右來排序間隔值時，boundary_value [ ,...n ] 屬於每個界限值間隔的哪一側 (左或右)。若未指定，LEFT 便是預設值。

備註

資料分割函數的範圍只限於建立它的資料庫。在這個資料庫內，資料分割函數是在不同於其他函數的個別命名空間中。

任何資料分割資料行含有 Null 值的資料列，都會放在最左側資料分割中，除非將 NULL 指定為界限值，且指示 RIGHT。在這個情況下，最左側的資料分割是空的資料分割，NULL 值會放在下列資料分割中。

權限

下列任何一個權限，都可以用來執行 CREATE PARTITION FUNCTION：

ALTER ANY DATASPACE 權限。這個權限預設為系統管理員 (sysadmin) 固定伺服器角色，以及 db_owner 和 db_ddladmin 固定資料庫角色的成員。
對於建立資料分割函數之資料庫的 CONTROL 或 ALTER 權限。
對於建立資料分割函數之資料庫伺服器的 CONTROL SERVER 或 ALTER ANY DATABASE 權限。

範例

A.建立 int 資料行的 RANGE LEFT 資料分割函數

下列資料分割函數會將資料表或索引分割成四份資料分割。

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行資料分割。

Partition	1	2	3	4
值	col1 <= 1	col1 > 1 AND col1 <= 100	col1 > 100 AND col1 <=1000	col1 > 1000

B.建立 int 資料行的 RANGE RIGHT 資料分割函數

下列資料分割函數使用前一個範例的相同 boundary_value [ ,...n ] 值，不過，它指定 RANGE RIGHT。

CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行資料分割。

Partition	1	2	3	4
值	col1 < 1	col1 >= 1 AND col1 < 100	col1 >= 100 AND col1 < 1000	col1 >= 1000

C.建立 datetime 資料行的 RANGE RIGHT 資料分割函數

下列資料分割函數會將資料表或是索引資料分割成為 12 個資料分割，分別在 datetime 資料行中顯示一年中各個月份的價值。

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
               '20030501', '20030601', '20030701', '20030801', 
               '20030901', '20031001', '20031101', '20031201');

下表顯示在分割資料行 datecol 上使用這個資料分割函數的資料表或索引如何進行資料分割。

Partition	1	2	...	11	12
值	datecol < February 1, 2003	datecol >= February 1, 2003 AND datecol < March 1, 2003		datecol >= November 1, 2003 AND col1 < December 1, 2003	datecol >= December 1, 2003

D.建立 char 資料行的資料分割函數

下列資料分割函數會將資料表或索引分割成四份資料分割。

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行資料分割。

Partition	1	2	3	4
值	col1 < EX...	col1 >= EX AND col1 < RXE...	col1 >= RXE AND col1 < XR...	col1 >= XR

E.建立 15,000 個資料分割

下列資料分割函數會將資料表或索引分割成 15,000 個資料分割。

--Create integer partition function for 15,000 partitions.
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
    SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
    SET @i += 1;    
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO

F.建立多個年度的資料分割

下列資料分割函數會將資料表或索引分割成 datetime2 資料行上的 50 個資料分割。 2007 年 1 月至 2011 年 1 月之間的每個月份都有一個資料分割。

--Create date partition function with increment by month.
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '20070101';
WHILE @i < '20110101'
BEGIN
    SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';
    SET @i = DATEADD(MM, 1, @i);    
END
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';
EXEC sp_executesql @DatePartitionFunction;
GO