Partitioned tables and indexes in SQL Server 2005

This article sample source code or material download

Summary: The table-based partitioning feature in SQL Server 2005 provides flexibility and better performance for simplifying the creation and maintenance of partitioned tables. Trace the evolution from the functionality of logical partitioned tables and manual partitioned tables to the latest partitioning features, and explore why, when, and how to use SQL Server 2005 to design, implement, and maintain partitioned tables.

Why do you want to partition?

What is a partition? Why should I use partitions? The simple answer is: to improve the scalability and manageability of large tables and tables with various access patterns. Typically, a table is created to store information about an entity, such as customers or sales, and each table has only attributes that describe that entity. A table that corresponds to an entity is the easiest to design and understand, so there is no need to optimize the performance, scalability, and manageability of this table, especially if the table becomes larger.

What does a large table consist of? The size of a very large database (VLDB) is calculated in hundreds of GB, or even in TB, but this term does not necessarily reflect the size of each table in the database. A large database is a database that cannot be run as expected, or a database running costs or maintenance costs that exceed predetermined maintenance requirements or budget requirements. These requirements also apply to tables, which can be considered very large if other users ' activities or maintenance operations limit the availability of data. For example, if the performance is severely degraded, or the data is inaccessible for two hours per day, weekly, or even monthly maintenance, the sales table can be considered very large. In some cases, periodic downtime is acceptable, but with better design and partitioning implementations, this can often be avoided or minimized. Although the term VLDB applies only to databases, it is more important for partitions to understand the size of the table.

In addition to size, tables with different access patterns can also affect performance and availability when different row sets in a table have different usage patterns. Although usage patterns are not always changing (this is also not a requirement for partitioning), partitioning can further improve management, performance, and availability when usage patterns change. In the case of a sales table, the data for the current month may be read-write, but the previous month's data (usually the majority of the table's data) is read-only. The ability of a table to respond to a user's request may be affected in a similar situation where data usage has changed, or when maintenance costs become unusually large as the number of reads and writes to the table increases. Correspondingly, this limits the availability and scalability of the server.