SQL Server Table Partitioning

The Table Partitioning technology introduced by SQL Server 2005 allows you to distribute data to different physical disks to improve the parallel processing performance of these disks to Optimize Query performance.

The SQL Server database table partition operation consists of three steps:

1. Create a partition function

2. Create a partition Architecture

3. Partition the table

The following describes each step in detail.

Step 1: Create a partition function

This partition function is used to define the parameter value ([u] How [/u]) of how you want SQL Server to partition data. This operation does not involve any tables, but simply defines a technology to split data.

We can define partitions by specifying the boundary conditions for each partition. For example, assume that we have a mers MERs table that contains information about all customers, which is distinguished by customer numbers (from 1 to 1,000,000. The following partition function is used to divide the table into four partitions of the same size:

Create Partition Function customer_partfunc (INT) As range right For values (250000,500 000, 750000)

These boundary values define four partitions. The first partition contains all data whose values are less than 250,000, and the second partition contains data between 250,000 and 49,999. The third partition contains data between 500,000 and 7499,999. All data with a value greater than or equal to 750,000 is classified into the fourth partition.

Note that the "range right" statement called here indicates that the boundary value of each partition is the right boundary. Similarly, if the "range left" statement is used, the first partition should include all data whose values are less than or equal to 250,000, the data value of the second partition is between 250,001 and 500,000, and so on.

Step 2: Create a partition Architecture

Once a partition function is provided to describe how to split data, a partition architecture is created to define the partition location ([u] Where [/u]). The creation process is straightforward. You just need to connect the partition to the specified file group. For example, if there are four file groups named from "FG1" to "fg4", the following Partition architecture can achieve the desired effect:

Create Partition Scheme customer_partscheme As partition customer_partfunc To (FG1, FG2, FG3, fg4)

Note that a partition function is connected to the partition architecture, but the partition architecture is not connected to any data table. This is where reusability works. No matter how many database tables there are, we can use this partition architecture (or just partition functions ).

Step 3: partition a table

After defining a partition architecture, you can create a partition table. This is the simplest step in the entire partition operation process. You only need to add an "on" Statement to the table creation command to specify the partition architecture and the table columns that apply the schema. Because the partition architecture has recognized the partition function, you do not need to specify the partition function.

For example, to create a customer table using the preceding partition architecture, you can call the following statements:

Create Table MERs (firstname nvarchar (40), lastname nvarchar (40), customernumber INT) On customer_partscheme (customernumber)

The Table Partitioning function of SQL Server is sufficient if you know the above knowledge. Remember! Writing General partition functions and partition architectures that can be used for multiple tables can greatly improve reusability.