Handling table partitioning problems in SQL Server 2005

This article describes the concept of table partitioning and the SQL Server 2005 Support table partition, which allows all table partitions to be saved on the same server. Each of the table partitions is associated with a single file in a filegroup (filegroup). The same file/filegroup can hold multiple partitioned tables. At the same time, a simple example is used to understand how table partitioning works.

Database Performance Tuning is the ultimate responsibility of every good SQL Server administrator. While ensuring the security and availability of data is our highest goal, DBAs will be blamed for poor performance design and implementation if the database application cannot meet the requirements of the user. SQL Server 2005 has been much improved in database performance, especially for table partitioning. If you are not aware of the features of the table partition, please take a moment to read this article.

The concept of table partitioning is not a new concept; As long as you are a SQL Server DBA for a while, you may have archived some frequently accessed tables when the historical data in the table is no longer frequently accessed. For example, if you have a print time report application, your report will rarely query for 1995 years because most of the budget planning is based on data from recent years.

In earlier versions of SQL Server, you could create multiple tables. Each table has the same column structure that holds the data for different years. Thus, when there is a need for access to historical data, you can create a view to query the tables. It is convenient to save data in more than one table because scanning the small table is faster than scanning the entire large table relative to the query. But the benefit is only when you know in advance which time period the data will be accessed. Also, once the data expires, you will need to create a new table and transfer the newly generated historical data.

SQL Server 7 and SQL Server 2000 support a distributed partitioned view (distributed partitioned views, also known as the materialized view, materialized). Distributed partitioned views consist of tables that have the same table structure distributed across multiple servers, and you also need to add a linked server definition for each server (linked server definitions), Finally, a view is created on one of the servers to merge the data returned on each server. The design idea here is that the database engine can use the processing power of multiple servers to satisfy the query.