SQL Server table partitions

Think twice about partitions:

1. Although partitioning can bring many benefits, it also increases the management cost and complexity of object implementation. Therefore, before partitioning, You need to carefully consider whether to partition the object.

2. After the partition is determined as an object, the next step is to determine the partition key and number of partitions. To determine the partition data, evaluate whether your data contains logical groups and modes.

3. Determine whether multiple file groups should be used. To help optimize performance and maintenance, you should use a file group to separate data. A file group is a logical combination of database files. It manages and distributes data files to improve the efficiency of concurrent access to database files.

To simplify the operation, Sqlserver2008 provides related operations for table partitions.

Operation Sequence:

1. Define a file group first

2. Specify which secondary database files belong to this file group.

3. Add the table to the file group.

Database File groups (specified disk ):

Data is actually stored in tables. We put the table into a file group, while a file group is a logical concept. In fact, the body is a secondary database file (ndr ), therefore, it is equivalent to placing the specified database into the specified secondary database file. If these secondary database files are placed in different disk partitions, in this way, the performance of the corresponding data can be optimized in a targeted manner.

When creating a file group, you can define different file group names to perform partition ing on the next table partition in an orderly manner, such as (select database, right-click to view the attribute map ).

A horizontal Partition Table has multiple partitions, and each partition corresponds to a file group. This results in many file groups, which also improves the performance, including I/O performance, because all partitions can be located on a different disk. Another advantage is that you can back up a partition separately through the backup file group. In addition, the SQL Server database engine can intelligently determine the partition on which data is stored. If more than one partition is accessed, you can use a multi-processor to retrieve data. This design also makes full use of the advantages of Partitioned Tables.

1. Improve scalability and management: In SqlServer2005, resume partitioning improves scalability and management of large tables and tables with various access modes.

2. improve performance.

3. Performance improvement can be achieved only when data is partitioned to different disks. Because multiple heads can read data at the same time during multi-Table link queries.

The process of partitioning an SQL server data table is divided into three steps:

1. Create a partition function

2. Create a partition scheme

3. Partition tables

The procedure is as follows: