SQL Server table Partition

Are you doing everything you can to optimize the performance of the SQL Server database? If your database contains a large number of tables, putting these table partitions into an independent file group may benefit you a lot. 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:

Partition creation can be:

Text, ntext, image, xml, timestamp, varchar (max), nvarchar (max), varbinary (max), alias data type, or CLR user-defined data type, all data types are valid.

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.

1 2 Next Page