How to Create Partition Function for Particular Column: SQL

A partition function specifies how a table should be partitioned. It specifies the range of values on a particular column. Based on which the table is partitioned. For example, in the scenario of Adventure Works, you can partition the data based on years. The following statement creates a partition function for the same:

CREATE PARTITION FUNCTION RateChangDate, (datetime)
AS RANGE RIGHT FOR VALUES (‘1996-01-01’, ‘2000-01-01’, ‘2004-01-01’, ‘2008-01-01’)

The preceding query creates a partition function named RateChangDate. It specifies that the data pertaining to the change in the payment rate will be partitioned based on the year.

Creating a Partition Scheme

After setting the partition function, you need to create the partition scheme. A partition scheme associates a partition function with various filegroups resulting in the physical layout of the data. Therefore, before creating a partition scheme, you need to create filegroups.

To create partition filegroups, you need to perform the following steps:

  • Expand the Database folder in the Object Explorer windows and right-click the AdventureWorks database.
  • Select the Properties option from the short-cut menu to display the Database Properties – AdventureWorks window.
  • Select the Filegroups folder from Select a page pane to display the list of all the filegroups in AdventureWorks.
  • Click the Add button to add a filegroup. Specify the name of the filegroup in the Name text box as Old.
  • Report Step 4 to add four more filegroups named First, Second, Third, and Fourth, as shown in the following figure.

    How to Create Partition Function for Particular Column: SQL
  • Select the Files folder from Select a page pane to display the list of all the files.
  • Click the Add button and type the name of the file as OldFile in the Logical Name text box, and select Old from the Filegroup drop-down list.
  • Report Step 7 to create four files names File1, File2, File3, File4, select filegroup as First, Second, Third, Fourth for the files.
  • Click OK button to close the Database Properties – AdventoreWorks window.

Execute the following statements in the Microsoft SQL Server Management
CREATE PARTITION SCHEME RateChangDate
AS PARTITION RateChangDate
TO (Old, First, Second, Third, Fourth)


Create partitioned table in sql

0 comments:

Post a Comment