Jerry Nixon @Work: Creating a simple Partitioned View over Horizontally Partitioned tables in SQL 2008.

Jerry Nixon on Windows

Tuesday, September 8, 2009

Creating a simple Partitioned View over Horizontally Partitioned tables in SQL 2008.

This isn’t special to SQL 2008; it works in SQL 2000. Imagine a table with so many rows that query operations are slow.

The easiest solution is Horizontal Partitioning – splitting your table based on the value in one or more columns (like all of this year’s invoices go in Invoices_2009, etc.) And the easiest, and maybe the only, way to create Horizontally Partitioned tables is using a Partitioned View – a single view over the Partitioned tables that unions back to one table again.

You can commit all your SELECT, UPDATE, INSERT, and DELETE operations solely against the Partitioned View – even if your Partitioned tables are on separate files, databases, or even servers.  It’s all so stinking easy. There are a few tricks; but, this sample below should get you moving right along.

image

Get the code here: http://www.codepaste.net/mqbfvb