System Enhancements for Administrators

SQL Server 2005 provides support for partitioned tables and indexes. This allows a table to be spread across a number of physical file groups, improving performance and making it easier to manage range-based archiving procedures.

You can partition a table horizontally so that the rows are spread across multiple file groups. Implement table partitioning to achieve the following results:

• Improved performance on multiprocessor systems

  Partitioned tables can increase performance when you are querying large tables on a system with eight or more processors. The SQL Server 2005 query optimizer can execute queries across multiple partitions in parallel, reducing the processing time required.

• Easier management of large tables

  A partitioned table allows easier management of data subsets, which you can group together logically—by a date range, for example. A common data requirement is to work with only the current month’s data.

You can specify the partitioning column, number of partitions, and the range of partitioning column values for each partition. You can use only one column from the table to distinguish the range partitions.

One benefit of partitioning is that you can move data between partitioned tables very quickly relative to performing an INSERT INTO SELECT FROM statement. This is because the data is not physically moved; only the metadata concerning storage location is changed.

In the AdventureWorks sample database, transaction information for the previous 12 months is stored in a Production.TransactionHistory table. Everything before that date is maintained in the Production.TransactionHistoryArchive table. Each month, the oldest month’s records for the current year are moved to the archive table, using partitioning based on the ModifiedDate field. Moving the partition is more efficient than physically copying and then deleting those records.

From:Microsoft
やってみたいです。