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.
To create partition filegroups, you need to perform the following steps:
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
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.
- 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