{"id":1880,"date":"2024-09-09T16:59:44","date_gmt":"2024-09-09T04:59:44","guid":{"rendered":"https:\/\/www.ronella.xyz\/?p=1880"},"modified":"2024-09-09T16:59:44","modified_gmt":"2024-09-09T04:59:44","slug":"creating-a-new-partitioned-table-in-sql-server-a-step-by-step-guide","status":"publish","type":"post","link":"https:\/\/www.ronella.xyz\/?p=1880","title":{"rendered":"Creating a New Partitioned Table in SQL Server: A Step-by-Step Guide"},"content":{"rendered":"<p>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.<\/p>\n<h2>1. Introduction to Table Partitioning<\/h2>\n<p>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.<\/p>\n<h2>2. Creating the Partition Function<\/h2>\n<p>The partition function determines how data is distributed across partitions. In our example, we will partition data based on <code>DATETIME<\/code> values, creating ranges for different years.<\/p>\n<pre><code class=\"language-sql\">CREATE PARTITION FUNCTION pf_orders_date_range (DATETIME)\nAS RANGE LEFT FOR VALUES (&#039;2011-01-01&#039;, &#039;2012-01-01&#039;, &#039;2013-01-01&#039;);<\/code><\/pre>\n<ul>\n<li><code>pf_orders_date_range<\/code> is the name of the partition function.<\/li>\n<li><code>RANGE LEFT<\/code> indicates that the range values specified are inclusive on the left and exclusive on the right.<\/li>\n<li>The function will create partitions for dates up to but not including January 1 of the subsequent years.<\/li>\n<\/ul>\n<h2>3. Adding Filegroups and Files<\/h2>\n<p>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.<\/p>\n<h3><strong>Adding Filegroups<\/strong><\/h3>\n<pre><code class=\"language-sql\">-- Add Filegroup for 2011\nALTER DATABASE advworks\nADD FILEGROUP fg_orders_201101;\n\n-- Add Filegroup for 2012\nALTER DATABASE advworks\nADD FILEGROUP fg_orders_201201;\n\n-- Add Filegroup for 2013\nALTER DATABASE advworks\nADD FILEGROUP fg_orders_201301;<\/code><\/pre>\n<h3><strong>Adding Files<\/strong><\/h3>\n<pre><code class=\"language-sql\">-- Add File for 2011\nALTER DATABASE advworks\nADD FILE \n(\n    NAME = &#039;Partition1_File&#039;,\n    FILENAME = &#039;C:\\tmp\\dummy\\fg_orders_201101.ndf&#039;,\n    SIZE = 100MB,\n    MAXSIZE = UNLIMITED,\n    FILEGROWTH = 10%\n)\nTO FILEGROUP fg_orders_201101;\n\n-- Add File for 2012\nALTER DATABASE advworks\nADD FILE \n(\n    NAME = &#039;Partition2_File&#039;,\n    FILENAME = &#039;C:\\tmp\\dummy\\fg_orders_201201.ndf&#039;,\n    SIZE = 100MB,\n    MAXSIZE = UNLIMITED,\n    FILEGROWTH = 10%\n)\nTO FILEGROUP fg_orders_201201;\n\n-- Add File for 2013\nALTER DATABASE advworks\nADD FILE \n(\n    NAME = &#039;Partition3_File&#039;,\n    FILENAME = &#039;C:\\tmp\\dummy\\fg_orders_201301.ndf&#039;,\n    SIZE = 100MB,\n    MAXSIZE = UNLIMITED,\n    FILEGROWTH = 10%\n)\nTO FILEGROUP fg_orders_201301;<\/code><\/pre>\n<ul>\n<li>Each <code>FILE<\/code> command creates a new file in the specified filegroup, with growth settings and initial size defined.<\/li>\n<\/ul>\n<h2>4. Creating the Partition Scheme<\/h2>\n<p>The partition scheme maps partitions to filegroups. This scheme will use the previously created partition function and filegroups.<\/p>\n<pre><code class=\"language-sql\">CREATE PARTITION SCHEME ps_orders_date_range  \nAS PARTITION pf_orders_date_range  \nTO (fg_orders_201101, fg_orders_201201, fg_orders_201301, [PRIMARY]);<\/code><\/pre>\n<ul>\n<li><code>ps_orders_date_range<\/code> is the name of the partition scheme.<\/li>\n<li>It maps the ranges defined in the partition function to the filegroups.<\/li>\n<\/ul>\n<h2>5. Creating the Partitioned Table<\/h2>\n<p>Finally, create the table and specify that it should use the partition scheme for data distribution.<\/p>\n<pre><code class=\"language-sql\">CREATE TABLE [Sales].[SalesOrderHeaderPartitioned](\n    [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,\n    [RevisionNumber] [tinyint] NOT NULL,\n    [OrderDate] [datetime] NOT NULL,\n    [DueDate] [datetime] NOT NULL,\n    [ShipDate] [datetime] NULL,\n    [Status] [tinyint] NOT NULL,\n    [OnlineOrderFlag] [dbo].[Flag] NOT NULL,\n    [SalesOrderNumber]  AS (isnull(N&#039;SO&#039;+CONVERT([nvarchar](23),[SalesOrderID]),N&#039;*** ERROR ***&#039;)),\n    [PurchaseOrderNumber] [dbo].[OrderNumber] NULL,\n    [AccountNumber] [dbo].[AccountNumber] NULL,\n    [CustomerID] [int] NOT NULL,\n    [SalesPersonID] [int] NULL,\n    [TerritoryID] [int] NULL,\n    [BillToAddressID] [int] NOT NULL,\n    [ShipToAddressID] [int] NOT NULL,\n    [ShipMethodID] [int] NOT NULL,\n    [CreditCardID] [int] NULL,\n    [CreditCardApprovalCode] [varchar](15) NULL,\n    [CurrencyRateID] [int] NULL,\n    [SubTotal] [money] NOT NULL,\n    [TaxAmt] [money] NOT NULL,\n    [Freight] [money] NOT NULL,\n    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),\n    [Comment] [nvarchar](128) NULL,\n    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,\n    [ModifiedDate] [datetime] NOT NULL,\n    CONSTRAINT [PK_SalesOrderHeaderPartitioned_SalesOrderID] PRIMARY KEY CLUSTERED (\n        [SalesOrderID] ASC,\n        [OrderDate] ASC -- Include OrderDate in the primary key\n    ) \n) ON ps_orders_date_range ([OrderDate]);<\/code><\/pre>\n<ul>\n<li>The <code>ON ps_orders_date_range ([OrderDate])<\/code> clause specifies that the table uses the partition scheme, distributing data based on the <code>OrderDate<\/code> column.<\/li>\n<\/ul>\n<h2>6. Verifying the Partition Setup<\/h2>\n<p>To ensure that the partitions are correctly set up, you can run the following query:<\/p>\n<pre><code class=\"language-sql\">SELECT \n    p.partition_number,\n    f.name AS file_group,\n    p.rows\nFROM sys.partitions p\nJOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id\nJOIN sys.filegroups f ON dds.data_space_id = f.data_space_id\nWHERE OBJECT_ID = OBJECT_ID(&#039;Sales.SalesOrderHeaderPartitioned&#039;)\nORDER BY p.partition_number;<\/code><\/pre>\n<ul>\n<li>This query provides information about partition numbers, associated filegroups, and the number of rows in each partition.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>Partitioning a table in SQL Server can significantly improve performance and ease data management. By following these steps\u2014creating a partition function, adding filegroups and files, setting up a partition scheme, creating the partitioned table, and verifying the setup\u2014you can efficiently manage large datasets and optimize query performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[58,46],"tags":[],"_links":{"self":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1880"}],"collection":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1880"}],"version-history":[{"count":1,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1880\/revisions"}],"predecessor-version":[{"id":1881,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1880\/revisions\/1881"}],"wp:attachment":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1880"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1880"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}