How to Create Partitioned Table in SQL Server

When the volume of data in a table increases and it takes time to query the data, you can partition the tables and store different parts of the tables in multiple physical locations based on a range of values for a specific column. This helps in managing the data and improving the query performance.

Consider the example of a manufacturing organization. The details of inventory movements are stored in the InventoryIssue table. The table contains a large volume of data and the queries take a lot of time to execute thereby slowing the report generation process.

To improve the query performance, you can partition the table to divide the data based on a condition and store different parts of the data in different locations. The condition can be based on the date of transaction and you can save the data pertaining to five years at a location. After partitioning the table, data can be retrieved directly from a particular partition by mentioning the partition number in the query.

In the preceding example, the partitioned table were created after the database had already been designed. You can also create partitioned tables while designing the database and creating tables. You can plan to create a partitioned table when you know that the data to be stored in the table will be large. For example, if you are creating a database for a banking application and you know that the transaction details will be voluminous, you can create the transaction table s with partitions.

Partitioning a table is only allowed in Enterprise Edition of SQL Server. To create a partitioned table, perform the following tasks:

  • Create a partition function.
  • Create a partition scheme.
  • Create a table by using the partition scheme.

For example, the AdventureWorks database stores the data of all the employees for the last 11 years. This data includes the personal details of the employees and their payment rates. Whenever there is a change in the payment rate of an employee, it is recorded in a separate record. However, this result in generation of large volume of data. This adversely affects the query performance.

To improve the query performance, the database developer needs to partition the table storing the changes in wage rate.

Create Rule and User-defined DataType

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google