建立資料分割資料表及索引

發行項
09/16/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

您可以使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server、Azure SQL Database 和 Azure SQL 受控執行個體中建立分割資料表或索引。分割資料表及索引中的資料會被水平分割成單元，可散佈在資料庫中的多個檔案群組中，或儲存在單一檔案群組中。分割作業可讓大型資料表和索引更容易管理及擴充。

分割資料表或索引的建立過程通常分為三或四個部分：

選擇性建立一個或多個檔案群組和對應的資料檔案，它們將存放分割配置所指定的分割。將分割放在多個檔案群組的主要原因是為了確保您可以獨立對檔案群組執行備份和還原作業。如果不需要，您可以選擇使用現有的檔案群組，例如 PRIMARY，或具有相關資料檔案的新檔案群組，將所有分割指派給單一檔案群組。在幾乎所有案例中，無論您是否使用多個檔案群組，您都會實現所有分割的優點。
建立資料分割函數，此函數根據指定之資料行的各個值，將資料表或索引的資料列對應到分割中。您可以使用單一資料分割函數來分割多個物件。
建立資料分割配置，將分割資料表或索引的分割對應至一或多個檔案群組。您可以使用單一資料分割配置來分割多個物件。
建立或改變資料表或索引，並將資料分割配置指定為儲存位置，以及作為分割資料行的資料行。

注意

Azure SQL Database 完全支援資料分割功能。由於 PRIMARYAzure SQL Database 僅支援檔案群組，因此所有分割區都必須放在PRIMARY檔案群組。

也可以在 Azure Synapse Analytics 的專用 SQL 集區中分割資料表，但語法會有一些差異。深入了解對專用 SQL 集區的資料分割資料表。

權限

建立分割區資料表，需要資料庫中的 CREATE TABLE 權限及建立資料表的結構描述之 ALTER 權限。建立分割區索引，需要建立索引的資料表或檢視的 ALTER 權限。建立分割區資料表或索引，還需要下列任何一個附加權限：

ALTER ANY DATASPACE 權限。這個權限預設會授與 sysadmin 固定伺服器角色以及 db_owner 和 db_ddladmin 固定資料庫角色的成員。
建立分割區函數和分割區配置之資料庫的 CONTROL 或 ALTER 權限。
建立分割區函數和分割區配置之資料庫伺服器的 CONTROL SERVER 或 ALTER ANY DATABASE 權限。

使用 Transact-SQL 在一個檔案群組上建立資料分割資料表

如果您不需要在檔案群組上獨立執行備份與還原作業，使用單一檔案群組分割資料表可簡化一段時間的資料分割資料表管理。

此範例適用於不支援新增檔案和檔案群組的 Azure SQL 資料庫。 Azure SQL Database 支援資料表分割，方法是在 PRIMARY 檔案群組中建立分割。針對 SQL Server 和 Azure SQL 受控執行個體，您可能會想要視檔案群組和檔案管理做法而定，指定使用者建立的檔案群組。

此範例會逐步說明如何使用 Transact-SQL 在 SQL Server Management Studio (SSMS) 中建立資料分割資料表，並將所有分割指派給 PRIMARY 檔案群組。範例：

使用 datetime2 資料類型，以三個界限值建立名為 myRangePF1 的 RANGE RIGHT 資料分割函數。三個界限值會導致具有四個分割的資料分割資料表。
建立名為 myRangePS1 的資料分割配置，此配置會使用 ALL TO 語法將 myRangePF1 資料分割函數中的所有分割指派給 PRIMARY 檔案群組。
在 myRangePS1 資料分割配置上建立名為 PartitionTable 的資料表，並將名為 col1 的資料行指定為分割資料行。

在物件總管中，連線到資料庫引擎的執行個體。
在標準列上，選取 [新增查詢]。
複製下列範例並將其貼到查詢視窗中，然後選取 [執行]。此範例會建立資料分割函數和資料分割配置。新資料表是以指定為儲存位置的分割區配置建立的。

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    ALL TO ('PRIMARY') ;
GO

CREATE TABLE dbo.PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))
    ON myRangePS1 (col1) ;
GO

使用 Transact-SQL 在多個檔案群組上建立資料分割資料表

遵循本節中的步驟，在 SSMS 中使用 Transact-SQL 建立一或多個檔案群組、對應的檔案和資料分割資料表。

SQL Server 和 Azure SQL 受控執行個體都支援建立檔案群組和檔案。 Azure SQL 受控執行個體會自動設定新增之所有資料庫檔案的路徑，因此 Azure SQL 受控執行個體中的 ALTER DATABASE ADD FILE 命令不允許使用 FILENAME 參數。 Azure SQL Database 僅支援在 PRIMARY 檔案群組中建立資料分割資料表。您可以在使用 Transact-SQL 在一個檔案群組上建立資料分割資料表中尋找 Azure SQL Database 的範例程式碼。

針對空的資料庫執行下列範例。範例：

將四個新的檔案群組新增至資料庫。
將一個檔案新增至每個檔案群組。
建立名為 myRangePF1 的 RANGE RIGHT 資料分割函數，其具有三個界限值，將資料表分割成四個分割區。
建立名為 myRangePS1 的資料分割配置，此配置會將 myRangePF1 套用至四個新的檔案群組。
建立名為 PartitionTable 的資料分割資料表，該資料表會使用 myRangePS1 分割 col1。

在物件總管中，連線到資料庫引擎的執行個體。
在 [標準] 工具列上，選取 [新增查詢]。

此範例會建立新的資料庫並加以使用。接著範例會建立新的檔案群組、資料分割函數和資料分割配置。新資料表是以指定為儲存位置的分割區配置建立的。將下列範例複製並貼入查詢視窗中。

如果您使用受控執行個體，請從 FILENAME 命令中移除 ALTER DATABASE ADD FILE 參數和相關聯的值。受控執行個體會自動為您決定檔案路徑。

如果您使用 SQL Server 執行個體，請將 FILENAME 參數的值自訂為適合執行個體的位置。

如果您想要使用現有的資料庫，請移除 CREATE DATABASE 命令，並將 USE 陳述式變更為適當的資料庫名稱。

選取 [執行]。

CREATE DATABASE PartitionTest;
GO

USE PartitionTest;
GO

ALTER DATABASE PartitionTest
ADD FILEGROUP test1fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test2fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test3fg;
GO
ALTER DATABASE PartitionTest
ADD FILEGROUP test4fg;

ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest1.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test1fg;
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest2.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test2fg;
GO
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest3.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test3fg;
GO
ALTER DATABASE PartitionTest
ADD FILE
(
    NAME = partitiontest4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest4.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP test4fg;
GO

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;
GO

CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    TO (test1fg, test2fg, test3fg, test4fg) ;
GO

CREATE TABLE PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))
    ON myRangePS1 (col1) ;
GO

使用 SSMS 分割資料表

遵循本節中的步驟，選擇性地建立檔案群組和對應的檔案，然後使用 SQL Server Management Studio (SSMS) 中的建立分割區精靈來建立資料分割資料表或分割現有的資料表。 SSMS 中提供適用於 SQL Server 和 Azure SQL 受控執行個體的建立分割區精靈。針對 Azure SQL Database，請參閱使用 Transact-SQL 在一個檔案群組上建立資料分割資料表。

建立新的檔案群組 (選擇性)

如果您想要將資料分割資料表放在一或多個新的檔案群組上，請遵循本節中的步驟。 SQL Server 和 Azure SQL 受控執行個體都支援建立檔案群組和檔案。針對 Azure SQL 受控執行個體，系統會自動為您設定任何已建立檔案的路徑。

在物件總管中，以滑鼠右鍵按一下要建立分割區資料表的資料庫，然後選取 [屬性]。
在 [資料庫屬性 - database_name] 對話方塊的 [選取頁面] 底下，選取 [檔案群組]。
在 [資料列] 底下，選取 [新增]。在新資料列中，輸入檔案群組名稱。

警告

建立分割區時，如果要指定多個檔案群組，除了針對界限值指定的檔案群組數目以外，一律要一個額外的檔案群組。
繼續加入資料列，直到為資料分割資料表或資料表建立所有的檔案群組。
選取 [確定]。
在 [選取頁面]底下，選取 [檔案]。
在 [資料列] 底下，選取 [新增]。在新資料列中，輸入檔案名稱並選取檔案群組。
繼續加入資料列，直到為每個檔案群組建立至少一個檔案。