looking for a good summary of SQL 2005 Partitioning

Question

I'm looking at this as a baseline explanation of the SQL 2005 Enterprise partitioning. Is there a resource that goes deeper into fine points and considerations of this issue. Some more examples would be useful too.

My main scenario is a time based partition system. With one partition that has the most accessed last X days. This partition will have to somehow slide (at least periodically) to keep it refereeing to the same amount of days.

Answer 1

What about this:

Partitioning Data for Query Performance - Where's the benefit?

Answer 2

I'm not sure if this will help you (how much data you are working with) but this whitepaper below speaks of how to use staging tables and the switch clause to alter partitions.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx

It's more about bulk loading data into partitions, but it might be worth a read. The example scenario as explained at the recent SQL Summit in Sydney, Australia used a date based partitioning scheme as the example, which might be similar to what your scenario is.

Answer 3

I've found the SQL Server 2005 Books Online normally has all the information I'm looking for.

I found a good resource in the 05 BOL on SQL Server 2005 Partitioning: http://technet.microsoft.com/en-us/library/ms188706(SQL.90).aspx

This link goes over designing partitioned tables and indexes: http://technet.microsoft.com/en-us/library/ms175533(SQL.90).aspx

Here is a blog post that explains the sliding window case you posted: http://blogs.msdn.com/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

Answer 4

Here's an excellant white paper on "SQL Server 2005 Partitioned Tables and Indexes" by Kimberly Tripp. http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

Answer 5

This site may help you:

http://highscalability.com/

specific tags:

http://highscalability.com/tags/shard

http://highscalability.com/tags/sharding