Reprint: SQL Server 2008-Build partition table (table Partition) reprint

The rationality of database structure and index affects the performance of database to a great extent, but with the increase of database information load, the performance of database is also greatly affected. Maybe our database has high performance at first, but with the rapid growth of data storage--such as order data--the performance of the data is also greatly affected, one obvious result is that the query response will be very slow. What else can you do at this time, in addition to optimizing your indexes and queries? Setting up a partitioned table (table Partition) can improve the performance of the database in some scenarios, and SQL Server 2005 can also create table partitions with the form of a wizard, but in SQL Server 2008, you create a partitioned table. This article describes how to create a partitioned table.

What is a partitioned table?

Partitioned tables are partitions of data into regions stored in different filegroups, using partitions to quickly and efficiently manage and access subsets of data, making large tables or indexes easier to manage. A reasonable use of partitioning can greatly improve the performance of your database. Partitioned tables and partitioned indexes are divided into cells that are distributed across multiple filegroups in a database. The data is partitioned horizontally, so multiple sets of rows are mapped to a single partition. Partitioned tables and partitioned indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, default values, identity and timestamp values, and triggers. Because the nature of partitioned tables is to store subsets of data that conform to different standards in one or more filegroups of a database, and to express data storage logical addresses through metadata.

Deciding whether to implement a partition depends primarily on the current size or future size of the table, how the table is used, and how well the user queries and maintenance operations are performed on the table. Typically, a large table may be suitable for partitioning if it meets the following two conditions:

• The table contains (or will contain) a large amount of data that is used in many different ways.
• You cannot perform a query or update on the table as expected, or the maintenance overhead exceeds the predefined maintenance period.

For example, if the data for the current month is primarily performed with INSERT, UPDATE, DELETE, and MERGE operations, and the data for the previous month is primarily a SELECT query, partitioning the table by month may make it easier to manage the tables. This is especially true if the normal maintenance operation on a table is only for a subset of data. If the table does not have partitions, you need to perform these operations on the entire dataset, which can consume a lot of resources. For example, with partitioning, maintenance operations like index rebuilds and defragmentation can be performed for a single month with write-only data, while read-only data is still available for online access.

Transact-SQL ALTER TABLE ... The SWITCH statement can also quickly and efficiently move a subset of data. This also provides a good convenience, so that we can rest assured of the full maintenance of the data sheet.

How do I create a partition table?

The Create partition table is divided into the following steps:

1. Create a partition function to specify how to partition the table that uses the function. (T-sql:create PARTITION FUNCTION)

2. Create a partition scheme to specify the location of the partition function's partition on the filegroup. (T-sql:create PARTITION SCHEMA)

3. Create a table that uses a partitioning scheme.

The following steps demonstrate how to complete the process of creating a partitioned table in SQL Server Management Studio through a wizard. Of course, you can also do these functions through scripting.

1. In SQL Server Management Studio, select the object you want to create a partitioned table from, right-click the table name and select Storage–> Create Partition.
2. Click the Next button in the Create Partition Wizard that appears to enter the Select a Partition column page.
3. In the Select a Partition column page, the data subset is set based on which field. Here we choose EndDate. In the back, the enddate is set to partition the data subset.

1. Enter a name for the function name on the Select a Partition function page. Here we enter yearorderpartitionfunction
2. The next step is to name the partition scheme you created. Enter Yearorderpartition as the name on the Select a Partition schema page.
3. On the Map partition page we set the partitioning standard. The left boundary and right boundary refer to the data subset that meets the criteria of distinction, corresponding to <= and <.
4. Click Set boundaries to set the data subset classification criteria based on EndDate.

1. Click Estimate storage to estimate the spatial size of each subset of data and its data entry. Based on the year-to-date criteria for all data set from 2001-01-01 to 2004-12-31, we ended up with 5 subsets of data and a subset of the latest data to store. This new subset of data is necessary for any partition, and each partition table contains this new subset of data to hold data that is different from all subsets of the data standard partition (here is the order greater than January 1, 2005).

1. Next, end the wizard by choosing which way to run (run immediately or schedule or script). Partition table creation is complete.

Creating a table partition is complete, then you can enjoy the advantages of table partitioning (if your choice is correct), you can merge, delete, move, and so on for each subset of data. With these you can maintain the database very well. We'll show you how to use partitioned tables later in the post.

Reprint: SQL Server 2008-Build partition table (table Partition) reprint