Partitioning a table can greatly enhance performance and manageability, particularly with large datasets. In this article, we will walk you through the process of creating a partitioned table in SQL Server using the AdventureWorks sample database. This practical example will illustrate how to set up a partitioned table based on order dates.
1. Introduction to Table Partitioning
Partitioning involves dividing a table into smaller, more manageable pieces, yet still presenting it as a single table to users. This is particularly useful for tables with a large volume of data, as it can improve query performance and make data management more efficient.
2. Creating the Partition Function
The partition function determines how data is distributed across partitions. In our example, we will partition data based on DATETIME values, creating ranges for different years.
CREATE PARTITION FUNCTION pf_orders_date_range (DATETIME)
AS RANGE LEFT FOR VALUES ('2011-01-01', '2012-01-01', '2013-01-01');
pf_orders_date_range is the name of the partition function.RANGE LEFT indicates that the range values specified are inclusive on the left and exclusive on the right.- The function will create partitions for dates up to but not including January 1 of the subsequent years.
3. Adding Filegroups and Files
Filegroups are used to organize data files and optimize storage. We will create three filegroups, each corresponding to a year, and then add data files to these filegroups.
Adding Filegroups
-- Add Filegroup for 2011
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201101;
-- Add Filegroup for 2012
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201201;
-- Add Filegroup for 2013
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201301;
Adding Files
-- Add File for 2011
ALTER DATABASE advworks
ADD FILE
(
NAME = 'Partition1_File',
FILENAME = 'C:\tmp\dummy\fg_orders_201101.ndf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201101;
-- Add File for 2012
ALTER DATABASE advworks
ADD FILE
(
NAME = 'Partition2_File',
FILENAME = 'C:\tmp\dummy\fg_orders_201201.ndf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201201;
-- Add File for 2013
ALTER DATABASE advworks
ADD FILE
(
NAME = 'Partition3_File',
FILENAME = 'C:\tmp\dummy\fg_orders_201301.ndf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201301;
- Each
FILE command creates a new file in the specified filegroup, with growth settings and initial size defined.
4. Creating the Partition Scheme
The partition scheme maps partitions to filegroups. This scheme will use the previously created partition function and filegroups.
CREATE PARTITION SCHEME ps_orders_date_range
AS PARTITION pf_orders_date_range
TO (fg_orders_201101, fg_orders_201201, fg_orders_201301, [PRIMARY]);
ps_orders_date_range is the name of the partition scheme.- It maps the ranges defined in the partition function to the filegroups.
5. Creating the Partitioned Table
Finally, create the table and specify that it should use the partition scheme for data distribution.
CREATE TABLE [Sales].[SalesOrderHeaderPartitioned](
[SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[RevisionNumber] [tinyint] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DueDate] [datetime] NOT NULL,
[ShipDate] [datetime] NULL,
[Status] [tinyint] NOT NULL,
[OnlineOrderFlag] [dbo].[Flag] NOT NULL,
[SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
[PurchaseOrderNumber] [dbo].[OrderNumber] NULL,
[AccountNumber] [dbo].[AccountNumber] NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[TerritoryID] [int] NULL,
[BillToAddressID] [int] NOT NULL,
[ShipToAddressID] [int] NOT NULL,
[ShipMethodID] [int] NOT NULL,
[CreditCardID] [int] NULL,
[CreditCardApprovalCode] [varchar](15) NULL,
[CurrencyRateID] [int] NULL,
[SubTotal] [money] NOT NULL,
[TaxAmt] [money] NOT NULL,
[Freight] [money] NOT NULL,
[TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
[Comment] [nvarchar](128) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderHeaderPartitioned_SalesOrderID] PRIMARY KEY CLUSTERED (
[SalesOrderID] ASC,
[OrderDate] ASC -- Include OrderDate in the primary key
)
) ON ps_orders_date_range ([OrderDate]);
- The
ON ps_orders_date_range ([OrderDate]) clause specifies that the table uses the partition scheme, distributing data based on the OrderDate column.
6. Verifying the Partition Setup
To ensure that the partitions are correctly set up, you can run the following query:
SELECT
p.partition_number,
f.name AS file_group,
p.rows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderHeaderPartitioned')
ORDER BY p.partition_number;
- This query provides information about partition numbers, associated filegroups, and the number of rows in each partition.
Conclusion
Partitioning a table in SQL Server can significantly improve performance and ease data management. By following these steps—creating a partition function, adding filegroups and files, setting up a partition scheme, creating the partitioned table, and verifying the setup—you can efficiently manage large datasets and optimize query performance.
Recent Comments