SQL Server 2005 Partitioned Tables and Indexes

http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

Summary: Although partitioning tables and indexes has always been a design tactic chosen to improve performance and manageability in larger databases, Microsoft SQL Server 2005 has new features that simplify the design. This whitepaper describes the logical progression from manually partitioning data by creating your own tables to the preliminary features, which enabled partitioning through views in SQL Server 7.0 and SQL Server 2000, to the true partitioned table features available in SQL Server 2005. In SQL Server 2005, the new table-based partitioning features significantly simplify design and administration of partitioned tables while continuing to improve performance. The paper's primary focus is to detail and document partitioning within SQL Server 2005 – giving you an understanding of why, when and how to use partitioned tables for the greatest benefit in your VLDB (Very Large Database). Although primarily a VLDB design strategy, not all databases start out large. SQL Server 2005 provides flexibility and performance while significantly simplifying the creation and maintenance of partitioned tables. Review this document to get detailed information about why you should consider partitioned tables, what they can offer and finally how to design, implement, and maintain partitioned tables.

Scripts from this Whitepaper:

The scripts and examples used in the code samples for this whitepaper can be found in the SQLServer2005PartitionedTables.zip file.

 

 

Table of Contents

Why do you need Partitioning?

The History of Partitioning

• Partitioning Objects manually in releases before SQL Server 7.0
• Partitioned Views in SQL Server 7.0
• Partitioned Views in SQL Server 2000
• Partitioned Tables in SQL Server 2005

Definitions and Terminology

• Range Partitions
• Defining the Partitioning Key
• Index Partitioning
• Special Conditions for Partitions – Split, Merge and Switch

Steps for Creating Partitioned Tables

• Determine IF Object should be partitioned
• Determine Partitioning Key and Number of Partitions
• Determine IF Multiple Filegroups should be used
• Create filegroups
• CREATE PARTITION FUNCTION for a Range Partition
• CREATE PARTITION SCHEME
• Create the partitioned table
• Create Indexes: Partitioned or Not?

Putting it all Together: Case Studies

• Range Partitioning - Sales Data
• Joining Partitioned Tables
• Sliding Window Scenario
• List Partitioning – Regional Data

Summary