What are the benefits of using partitions with the Enterprise edition of SQL 2005

Question

I'm comparing between two techniques to create partitioned tables in SQL 2005.

• Use partitioned views with a standard version of SQL 2005 (described here)
• Use the built in partition in the Enterprise edition of SQL 2005 (described here)

Given that the enterprise edition is much more expensive, I would like to know what are the main benefits of the newer enterprise built-in implementation. Is it just an time saver for the implementation itself. Or will I gain real performance on large DBs?

I know i can adjust the constraints in the first option to keep a sliding window into the partitions. Can I do it with the built in version?

Answer 1

searchdotnet rulz! check this out:

http://www.eggheadcafe.com/forumarchives/SQLServerdatawarehouse/Dec2005/post25052042.asp

Answer 2

When using the partitioned tables you can more easily move data from partition to partition. You can also partition the indexes as well.

You can also move data from one partition to another table as needed with a single ALTER TABLE command.

Answer 3

You can find a comprehensive answer to your question in Kimberly L. Tripp's writing SQL Server 2005 Partitioned Tables and Indexes

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.