ALTER PARTITION FUNCTION (Transact-SQL)ALTER 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

藉由分割或合併資料分割函數的界限值來變更資料分割函數。Alters a partition function by splitting or merging its boundary values. 執行 ALTER PARTITION FUNCTION 陳述式,可將一個使用資料分割函數的資料表分割區或索引分割為兩個分割區。Running an ALTER PARTITION FUNCTION statement can split one table partition or index that uses the partition function into two partitions. 此陳述式也可以將兩個分割區合併成一個分割區。The statement can also merge two partitions into one less partition.

警告

多份資料表或索引可以使用相同的資料分割函數。More than one table or index can use the same partition function. ALTER PARTITION FUNCTION 會在單一交易中影響所有的資料表或索引。ALTER PARTITION FUNCTION affects all of them in a single transaction.

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

語法Syntax

  
ALTER PARTITION FUNCTION partition_function_name()  
{   
    SPLIT RANGE ( boundary_value )  
  | MERGE RANGE ( boundary_value )   
} [ ; ]  

引數Arguments

partition_function_namepartition_function_name
這是您要修改的資料分割函數名稱。Is the name of the partition function to be modified.

SPLIT RANGE ( boundary_value )SPLIT RANGE ( boundary_value )
將一個資料分割加入資料分割函數。Adds one partition to the partition function. boundary_value 會決定新資料分割的範圍,而此範圍必須不同於資料分割函數現有的界限範圍。boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Database EngineDatabase Engine 會以 boundary_value 為基礎,將現有的一個範圍分割成兩個。Based on boundary_value, the Database EngineDatabase Engine splits one of the existing ranges into two. 在這兩個範圍中,具有新 boundary_value 的範圍是新的分割區。Of these two ranges, the one with the new boundary_value is the new partition.

線上必須有檔案群組存在。A filegroup must exist online. 而且,使用資料分割函數作為 NEXT USED 以保存新分割區的資料分割配置必須標示檔案群組。And, the partition scheme that uses the partition function as NEXT USED to hold the new partition must mark the filegroup. CREATE PARTITION SCHEME 陳述式會將檔案群組指派給分割區。A CREATE PARTITION SCHEME statement assigns filegroups to partitions. CREATE PARTITION FUNCTION 陳述式會建立少於檔案群組數目的分割區數目以利保存。The CREATE PARTITION FUNCTION statement creates fewer partitions than filegroups to hold them. CREATE PARTITION SCHEME 陳述式所保留的檔案群組數目可能比所需的還多。A CREATE PARTITION SCHEME statement may set aside more filegroups than needed. 如果發生這種情況,則您最終將不會獲指派檔案群組。If that happens, then you'll end up with unassigned filegroups. 此外,資料分割配置會將其中一個檔案群組標示為 NEXT USED。Also, the partition scheme marks one of the filegroups as NEXT USED. 這個檔案群組會保存新的分割區。This filegroup holds the new partition. 如果沒有資料分割配置標示為 NEXT USED 的檔案群組,則您必須使用 ALTER PARTITION SCHEME 陳述式。If there are no filegroups the partition scheme marks as NEXT USED, you must use an ALTER PARTITION SCHEME statement.

ALTER PARTITION SCHEME 陳述式可以新增檔案群組或選取現有的檔案群組,來保存新的分割區。The ALTER PARTITION SCHEME statement can either add a filegroup, or select an existing one, to hold the new partition. 您可以指定已經保存分割區的檔案群組來保存其他分割區。You can assign a filegroup that already holds partitions to hold additional partitions. 資料分割函數可以參與多個資料分割配置。A partition function can participate in more than one partition scheme. 基於這個理由,使用您要新增分割區之資料分割函數的所有資料分割配置都必須具有 NEXT USED 檔案群組。For this reason, all the partition schemes that use the partition function to which you're adding partitions must have a NEXT USED filegroup. 否則,ALTER PARTITION FUNCTION 陳述式就會失敗並出現一則錯誤,顯示一個或多個缺少 NEXT USED 檔案群組的資料分割配置。Otherwise, the ALTER PARTITION FUNCTION statement fails with an error that displays the partition scheme or schemes that lack a NEXT USED filegroup.

如果您在相同的檔案群組中建立所有資料分割,會在一開始時自動將該檔案群組指派為 NEXT USED 檔案群組。If you create all the partitions in the same filegroup, that filegroup is initially assigned to be the NEXT USED filegroup automatically. 但在分割作業執行之後,即不再有選取的 NEXT USED 檔案群組。However, after a split operation runs, there's no longer a selected NEXT USED filegroup. 使用 ALTER PARTITION SCHEME 來將檔案群組明確指派為 NEXT USED 檔案群組,否則後續的分割作業將會失敗。Explicitly assign the filegroup as the NEXT USED filegroup by using ALTER PARTITION SCHEME or an upcoming split operation will fail.

注意

資料行存放區索引的限制:當資料表上存在資料行存放區索引時,僅能分割空的分割區。Limitations with columnstore index: Only empty partitions can be split in when a columnstore index exists on the table. 執行此作業之前,您必須先卸除或停用資料行存放區索引。You will need to drop or disable the columnstore index before performing this operation.

MERGE [ RANGE ( boundary_value) ]MERGE [ RANGE ( boundary_value) ]
卸除分割區,並將該分割區中現有的所有值合併到剩餘的分割區中。Drops a partition and merges any values that exist in the partition into a remaining partition. RANGE (boundary_value) 必須是現有的界限值,已卸除之資料分割中的值會合併到其中。RANGE (boundary_value) must be an existing boundary value, into which the values from the dropped partition are merged. 除非有剩餘的分割區會使用最初保存 boundary_value 的檔案群組,或者已用 NEXT USED 屬性來標示這個檔案群組,否則,此引數會從資料分割配置中移除該檔案群組。This argument removes the filegroup that originally held boundary_value from the partition scheme unless a remaining partition uses it, or marks it with the NEXT USED property. 合併的分割區存在於一開始未保存 boundary_value 的檔案群組中。The merged partition exists in the filegroup that didn't hold boundary_value at first. boundary_value 是可以參考變數 (包括使用者定義型別變數) 或函數 (包括使用者自訂函數) 的常數運算式。boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including user-defined functions). 它無法參考 Transact-SQLTransact-SQL 運算式。It can't reference a Transact-SQLTransact-SQL expression. boundary_value 必須完全符合或可隱含轉換成其對應分割資料行的資料類型。boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column. 您也無法以值的大小和小數位數不符合其對應 input_parameter_type 之值的方式,在隱含轉換期間截斷 boundary_valueYou also can't truncate boundary_value during implicit conversion in a way that the size and scale of the value doesn't match that of its corresponding input_parameter_type.

注意

資料行存放區索引的限制:包含資料行存放區索引的兩個非空白分割區無法合併。Limitations with columnstore index: Two nonempty partitions containing a columnstore index can't be merged. 在執行此作業之前,您必須卸除或停用資料行存放區索引You will need to drop or disable the columnstore index before performing this operation

最佳做法Best Practices

一律在分割區範圍的兩端保留空的分割區。Always keep empty partitions at both ends of the partition range. 在這兩端保留分割區,以確保分割區會分割,而且分割區合併不會產生任何資料移動。Keep the partitions at both ends to guarantee that the partition split and the partition merge don't incur any data movement. 分割區分割會在一開始發生,而分割區合併會在結束時發生。The partition split occurs at the beginning and the partition merge occurs at the end. 請避免分割或合併已擴展的資料分割。Avoid splitting or merging populated partitions. 分割或合併已擴展的分割區有時候很沒效率。Splitting or merging populated partitions can be inefficient. 之所以缺乏效率,是因為分割與合併有時能使記錄產生作業延長四倍以上,也可能造成嚴重鎖定。They can be inefficient because the split or merge may cause as much as four times more log generation, and may also cause severe locking.

限制事項Limitations and Restrictions

ALTER PARTITION FUNCTION 會在單一不可部分完成的作業中,重新分割任何使用函數的資料表和索引。ALTER PARTITION FUNCTION repartitions any tables and indexes that use the function in a single atomic operation. 不過,這項作業是離線進行的,可能非常需要資源,這會隨著重新分割的範圍而不同。However, this operation occurs offline, and depending on the extent of repartitioning, may be resource-intensive.

只使用 ALTER PARTITION FUNCTION 來將一個分割區分成兩個,或將兩個分割區合併成一個。Only use ALTER PARTITION FUNCTION for splitting one partition into two, or merging two partitions into one. 若要變更資料表的分割方式 (例如,從 10 個分割區到五個分割區),請練習下列任意選項。To change the way a table is otherwise partitioned (for example, from 10 partitions to five partitions), exercise any of the following options. 這些選項的資源耗用程度各異,可能會隨著系統的設定而不同:Depending on the configuration of your system, these options may vary in resource consumption:

  • 使用所需的資料分割函數來建立新的資料分割資料表。Create a new partitioned table with the necessary partition function. 然後,使用 INSERT INTO...SELECT FROM 陳述式,將舊資料表的資料插入新的資料表。Then, insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.

  • 建立堆積的資料分割叢集索引。Create a partitioned clustered index on a heap.

    注意

    卸除資料分割叢集索引會產生資料分割堆積。Dropping a partitioned clustered index results in a partitioned heap.

  • 利用設定了 DROP EXISTING = ON 子句的 Transact-SQLTransact-SQL CREATE INDEX 陳述式來卸除和重建現有的資料分割索引。Drop and rebuild an existing partitioned index by using the Transact-SQLTransact-SQL CREATE INDEX statement with the DROP EXISTING = ON clause.

  • 執行 ALTER PARTITION FUNCTION 陳述式的序列。Run a sequence of ALTER PARTITION FUNCTION statements.

ALTER PARTITION FUNCTION 所影響的所有檔案群組都必須在線上。All filegroups that are affected by ALTER PARTITION FUNCTION must be online.

當使用資料分割函數的任何資料表上有已停用的叢集索引時,ALTER PARTITION FUNCTION 就會失敗。ALTER PARTITION FUNCTION fails when a disabled clustered index exists on any tables that use the partition function.

SQL ServerSQL Server 不提供修改資料分割函數的複寫支援。doesn't provide replication support for modifying a partition function. 您必須在訂閱資料庫中,手動套用發行集資料庫中的資料分割函數變更。Changes to a partition function in the publication database must be manually applied in the subscription database.

權限Permissions

下列中的任何權限都可用來執行 ALTER PARTITION FUNCTION:Any one of the following permissions can be used to execute ALTER 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 was created.

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

範例Examples

A.A. 將資料分割或分割資料表或索引的分割分成兩個資料分割Splitting a partition of a partitioned table or index into two partitions

下列範例會建立一個資料分割函數,將資料表或索引分割成四個資料分割。The following example creates a partition function to partition a table or index into four partitions. ALTER PARTITION FUNCTION 會將其中一個資料分割分成兩個,以建立總計五個的資料分割。ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions.

IF EXISTS (SELECT * FROM sys.partition_functions  
    WHERE name = 'myRangePF1')  
DROP PARTITION FUNCTION myRangePF1;  
GO  
CREATE PARTITION FUNCTION myRangePF1 (int)  
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );  
GO  
--Split the partition between boundary_values 100 and 1000  
--to create two partitions between boundary_values 100 and 500  
--and between boundary_values 500 and 1000.  
ALTER PARTITION FUNCTION myRangePF1 ()  
SPLIT RANGE (500);  

B.B. 將分割資料表的兩個資料分割合併成一個資料分割Merging two partitions of a partitioned table into one partition

下列範例會建立上述中的相同資料分割函數,再將兩份資料分割合併成一份資料分割,總共三份的資料分割。The following example creates the same partition function as above, and then merges two of the partitions into one partition, for a total of three partitions.

IF EXISTS (SELECT * FROM sys.partition_functions  
    WHERE name = 'myRangePF1')  
DROP PARTITION FUNCTION myRangePF1;  
GO  
CREATE PARTITION FUNCTION myRangePF1 (int)  
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );  
GO  
--Merge the partitions between boundary_values 1 and 100  
--and between boundary_values 100 and 1000 to create one partition  
--between boundary_values 1 and 1000.  
ALTER PARTITION FUNCTION myRangePF1 ()  
MERGE RANGE (100);  

另請參閱See Also

資料分割資料表與索引 Partitioned Tables and Indexes
CREATE PARTITION FUNCTION (Transact-SQL) CREATE PARTITION FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL) ALTER PARTITION SCHEME (Transact-SQL)
DROP PARTITION SCHEME (Transact-SQL) DROP PARTITION SCHEME (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
sys.partition_functions (Transact-SQL) sys.partition_functions (Transact-SQL)
sys.partition_parameters (Transact-SQL) sys.partition_range_values (Transact-SQL) sys.partition_parameters (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)