CREATE PARTITION FUNCTION (Transact-SQL)CREATE PARTITION FUNCTION (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

在目前資料庫中建立一個函數,根據指定資料行的各個值,將資料表或索引的資料列對應到資料分割中。Creates a function in the current database that maps the rows of a table or index into partitions based on the values of a specified column. 使用 CREATE PARTITION FUNCTION 是建立資料分割資料表或索引的第一步。Using CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,一個資料表或索引最多可以有 15,000 個資料分割。In SQL Server 2019 (15.x)SQL Server 2019 (15.x), a table or index can have a maximum of 15,000 partitions.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

partition_function_namepartition_function_name
這是資料分割函數的名稱。Is the name of the partition function. 資料分割函數名稱在資料庫內必須是唯一的,且必須符合識別碼的規則。Partition function names must be unique within the database and comply with the rules for identifiers.

input_parameter_typeinput_parameter_type
這是資料分割所用之資料行的資料類型。Is the data type of the column used for partitioning. 除了 textntextimagexmltimestampvarchar(max)nvarchar(max)varbinary(max) 、別名資料類型或 CLR 使用者自訂資料類型,所有資料類型都能有效用在分割資料行上。All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

實際資料行稱為「資料分割資料行」,指定在 CREATE TABLE 或 CREATE INDEX 陳述式中。The actual column, known as a partitioning column, is specified in the CREATE TABLE or CREATE INDEX statement.

boundary_valueboundary_value
針對每個資料分割資料表的資料分割或使用 partition_function_name 的索引指定界限值。Specifies the boundary values for each partition of a partitioned table or index that uses partition_function_name. 如果 boundary_value 為空白,資料分割函數會使用 partition_function_name,將整個資料表或索引對應到單一資料分割。If boundary_value is empty, the partition function maps the whole table or index using partition_function_name into a single partition. 只能使用 CREATE TABLE 或 CREATE INDEX 陳述式中所指定的一個資料分割資料行。Only one partitioning column, specified in a CREATE TABLE or CREATE INDEX statement, can be used.

boundary_value 是可以參考變數的常數運算式。boundary_value is a constant expression that can reference variables. 其中包括使用者自訂類型變數,或函數和使用者自訂函數。This includes user-defined type variables, or functions and user-defined functions. 它不能參考 Transact-SQLTransact-SQL 運算式。It cannot reference Transact-SQLTransact-SQL expressions. boundary_value 必須符合或可以隱含地轉換成 input_parameter_type 中提供的資料類型,且在隱含地轉換期間,不能因為值的大小和小數位數不符合對應 input_parameter_type 的大小和小數位數而被截斷。boundary_value must either match or be implicitly convertible to the data type supplied in input_parameter_type, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type.

注意

如果 boundary_valuedatetimesmalldatetime 常值組成,則評估這些常值時會假設 us_english 為工作階段語言。If boundary_value consists of datetime or smalldatetime literals, these literals are evaluated assuming that us_english is the session language. 這個行為已被取代。This behavior is deprecated. 為了確定使用所有工作階段語言時資料分割函數定義的行為都可如所預期,建議您使用所有語言設定都會解譯成相同內容的常數,例如 yyyymmdd 格式;或是將常值明確轉換成特定樣式。To make sure the partition function definition behaves as expected for all session languages, we recommend that you use constants that are interpreted the same way for all language settings, such as the yyyymmdd format; or explicitly convert literals to a specific style. 若要判斷伺服器的工作階段語言,請執行 SELECT @@LANGUAGETo determine the language session of your server, run SELECT @@LANGUAGE.

如需詳細資訊,請參閱將常值日期字串轉換成 DATE 值的非決定性轉換For more information, see Nondeterministic conversion of literal date strings into DATE values.

...n...n
指定 boundary_value 所提供的數目值,但不可超過 14,999。Specifies the number of values supplied by boundary_value, not to exceed 14,999. 所建立的資料分割數目等於 n + 1。The number of partitions created is equal to n + 1. 這些值不必依照順序列出。The values do not have to be listed in order. 如果值沒有排序,Database EngineDatabase Engine 會將它們排序、建立函數,以及傳回未依序提供值的警告。If the values are not in order, the Database EngineDatabase Engine sorts them, creates the function, and returns a warning that the values are not provided in order. 如果 n 包括任何重複的值,「資料庫引擎」會傳回錯誤。The Database Engine returns an error if n includes any duplicate values.

LEFT | RIGHTLEFT | RIGHT
指定當 是按遞增順序由左至右來排序間隔值時, boundary_value [ , ...nDatabase EngineDatabase Engine ] 屬於每個界限值間隔的哪一側 (左或右)。Specifies to which side of each boundary value interval, left or right, the boundary_value [ ,...n ] belongs, when interval values are sorted by the Database EngineDatabase Engine in ascending order from left to right. 若未指定,LEFT 便是預設值。If not specified, LEFT is the default.

備註Remarks

資料分割函數的範圍只限於建立它的資料庫。The scope of a partition function is limited to the database that it is created in. 在這個資料庫內,資料分割函數是在不同於其他函數的個別命名空間中。Within the database, partition functions reside in a separate namespace from the other functions.

任何資料分割資料行含有 Null 值的資料列,都會放在最左側資料分割中,除非將 NULL 指定為界限值,且指示 RIGHT。Any rows whose partitioning column has null values are placed in the left-most partition, unless NULL is specified as a boundary value and RIGHT is indicated. 在這個情況下,最左側的資料分割是空的資料分割,NULL 值會放在下列資料分割中。In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition.

權限Permissions

下列任何一個權限,都可以用來執行 CREATE PARTITION FUNCTION:Any one of the following permissions can be used to execute CREATE PARTITION FUNCTION:

  • ALTER ANY DATASPACE 權限。ALTER ANY DATASPACE permission. 這個權限預設會授與 sysadmin 固定伺服器角色以及 db_ownerdb_ddladmin 固定資料庫角色的成員。This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.

  • 對於建立資料分割函數之資料庫的 CONTROL 或 ALTER 權限。CONTROL or ALTER permission on the database in which the partition function is being created.

  • 對於建立資料分割函數之資料庫伺服器的 CONTROL SERVER 或 ALTER ANY DATABASE 權限。CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition function is being created.

範例Examples

A.A. 建立 int 資料行的 RANGE LEFT 資料分割函數Creating a RANGE LEFT partition function on an int column

下列資料分割函數會將資料表或索引分割成四份資料分割。The following partition function will partition a table or index into four partitions.

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

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行分割。The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

資料分割Partition 11 22 33 44
Values col1 <= 1col1 <= 1 col1 > 1 AND col1 <= 100col1 > 1 AND col1 <= 100 col1 > 100 AND col1 <=1000col1 > 100 AND col1 <=1000 col1 > 1000col1 > 1000

B.B. 建立 int 資料行的 RANGE RIGHT 資料分割函數Creating a RANGE RIGHT partition function on an int column

下列資料分割函數使用前一個範例的相同 boundary_value [ , ...n ] 值,不過,它指定 RANGE RIGHT。The following partition function uses the same values for boundary_value [ ,...n ] as the previous example, except it specifies RANGE RIGHT.

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

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行分割。The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

資料分割Partition 11 22 33 44
Values col1 < 1col1 < 1 col1 >= 1col1 < 100col1 >= 1 AND col1 < 100 col1 >= 100col1 < 1000col1 >= 100 AND col1 < 1000 col1 >= 1000col1 >= 1000

C.C. 建立 datetime 資料行的 RANGE RIGHT 資料分割函數Creating a RANGE RIGHT partition function on a datetime column

下列資料分割函數會將資料表或是索引分割成為 12 個資料分割,分別在 datetime 資料行中顯示一年中各個月份的價值。The following partition function partitions a table or index into 12 partitions, one for each month of a year's worth of values in a datetime column.

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

下表顯示在分割資料行 datecol 上使用這個資料分割函數的資料表或索引如何進行分割。The following table shows how a table or index that uses this partition function on partitioning column datecol would be partitioned.

資料分割Partition 11 22 ...... 1111 1212
Values datecol < February 1, 2003datecol < February 1, 2003 datecol >= February 1, 2003datecol < March 1, 2003datecol >= February 1, 2003 AND datecol < March 1, 2003 datecol >= November 1, 2003col1 < December 1, 2003datecol >= November 1, 2003 AND col1 < December 1, 2003 datecol >= December 1, 2003datecol >= December 1, 2003

D.D. 建立 char 資料行的資料分割函數Creating a partition function on a char column

下列資料分割函數會將資料表或索引分割成四份資料分割。The following partition function partitions a table or index into four partitions.

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

下表顯示在分割資料行 col1 上使用這個資料分割函數的資料表如何進行分割。The following table shows how a table that uses this partition function on partitioning column col1 would be partitioned.

資料分割Partition 11 22 33 44
Values col1 < EX...col1 < EX... col1 >= EXcol1 < RXE...col1 >= EX AND col1 < RXE... col1 >= RXEcol1 < XR...col1 >= RXE AND col1 < XR... col1 >= XRcol1 >= XR

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

下列資料分割函數會將資料表或索引分割成 15,000 個資料分割。The following partition function partitions a table or index into 15,000 partitions.

--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.F. 建立多個年度的資料分割Creating partitions for multiple years

下列資料分割函數會將資料表或索引分割成 datetime2 資料行上的 50 個資料分割。The following partition function partitions a table or index into 50 partitions on a datetime2 column. 2007 年 1 月至 2011 年 1 月之間的每個月份都有一個資料分割。There is one partitions for each month between January 2007 and January 2011.

--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  

另請參閱See Also

資料分割資料表與索引 Partitioned Tables and Indexes
$PARTITION (Transact-SQL) $PARTITION (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL) ALTER PARTITION FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.partition_functions (Transact-SQL) sys.partition_functions (Transact-SQL)
sys.partition_parameters (Transact-SQL) sys.partition_parameters (Transact-SQL)
sys.partition_range_values (Transact-SQL) sys.partition_range_values (Transact-SQL)
sys.partitions (Transact-SQL) sys.partitions (Transact-SQL)
sys.tables (Transact-SQL) sys.tables (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)sys.index_columns (Transact-SQL)