SQL Server table partitioning _ MySQL

Think twice about partitions: 1. although partitions can bring many benefits, they also increase management fees and complexity for implementing objects. Therefore, before partitioning, you need to carefully consider whether to partition the object. 2. after determining to partition the 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. Text 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 determining to partition the 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 data files. it can manage and allocate data files to improve the efficiency of concurrent access to database files.

To simplify the operation, SQL Server 2008 provides related operations for Table partitions.

Operation sequence:

　　1. define a file group first

2. specify which auxiliary 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 data in 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 for 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 which partition stores the data. if more than one partition is accessed, you can also use multi-processor to implement parallel data retrieval. This design also makes full use of the advantages of partitioned tables.

1. improve scalability and manageability: create partitions in SQL Server 2005 to improve scalability and manageability of large tables and tables with various access modes.

2. improve performance

3. data partitions can be greatly improved only when they are allocated to different disks.

4. when you run a query involving table connection, multiple headers can read data at the same time.

The SQL Server Data table partition process is divided into three steps:

1) create a partition function

2) create a partition scheme

3) partition the table

The procedure is as follows: