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.

-- clean up any test artifacts
if object_id('test1') is not null drop table test1
if object_id('test2') is not null drop table test2
if object_id('test') is not null drop view test
GO
 



-- the partition column must have a CHECK and be part of the PRIMARY KEY
create table test1 (col1 int primary key check (col1 = 1), col2 varchar(50))
GO
 
-- the partition column must have a CHECK and be part of the PRIMARY KEY
create table test2 (col1 int primary key check (col1 = 2), col2 varchar(50))
GO
 
-- the partition view must use UNION ALL
create view test as 
    select * from test1 union all
    select * from test2
GO
 
-- add a record to each, let the view determine the correct table
insert into test select 1, 'one'
insert into test select 2, 'two'
GO
 
-- take a look, it's all partitioned correctly
select * from test1
select * from test2
GO
 
-- clear the table for another test
delete from test
-- insert into the first table,, like we did above
insert into test select 1, 'one'
-- now let the view move it to the other table with an update
update test set col1 = 2 where col1  = 1
GO
 
-- take a look, it's all moved like we want; magic
select * from test1
select * from test2