Partitioned Views in SQL 2008

A partitioned view is a view that unifies multiple identical (in terms of structure — not actual data) tables and makes them appear to be a single table. At first, this seems like an easy thing to do with simple UNION clauses, but the concept actually becomes somewhat tricky when you go to handle insert and update scenarios.

With partitioned views, we define a constraint on one of the tables in our view. We then define a similar, but mutually exclusive, constraint on a second (and possibly many more) table. When you build the view that unifies these mutually exclusive tables, SQL Server is able to sort out the exclusive nature of the tables in a logical manner. By doing this, SQL Server can determine exactly which table is to get the new data (by determining which table can accept the data — if you created them as mutually exclusive as you should have, then the data will be able to get into only one table and there is no conflict). The only catch is that the so called ‘‘partitioning column’’ must participate in the primary key.

Here’s what a two-month set of data might look:

CREATE TABLE OrderPartitionJan08(

OrderID int NOT NULL,

OrderDate date NOT NULL

CONSTRAINT CKIsJanOrder CHECK (OrderDate >= ’2008/01/01’ AND OrderDate < ’2008/02/01’),

CustomerID int NOT NULL,

CONSTRAINT PKOrderIDOrderDateJan PRIMARY KEY (OrderID, OrderDate)

);

CREATE TABLE OrderPartitionFeb08(

OrderID int NOT NULL,

OrderDate date NOT NULL

CONSTRAINT CKIsFebOrder CHECK (OrderDate >= ’2008/02/01’ AND OrderDate < ’2008/03/01’),

CustomerID int NOT NULL,

CONSTRAINT PKOrderIDOrderDateFeb PRIMARY KEY (OrderID, OrderDate)

);

GO

CREATE VIEW Orders

AS

SELECT *

FROM OrderPartitionJan08

UNION ALL

SELECT *

FROM OrderPartitionFeb08;

Once we have created these tables along with the view that unites them into a partitioned view, we’re

ready to insert a few rows of data:

INSERT INTO Orders VALUES

(1, ’2008-01-15’, 1),

(2, ’2008-02-15’, 1);

Orders is a view, and therefore has no data of its own — so where does the data go? Under the covers,

SQL Server analyzes the data being inserted and figures out that, based on the constraints in our table, the data can, in each case, go to one and only one table. Let’s check that out with a few queries:

SELECT * FROM Orders;

SELECT * FROM OrderPartitionJan08;

SELECT * FROM OrderPartitionFeb08;

This gets us, in order, both rows we inserted, then the row from January, then the one from February.

OrderID OrderDate CustomerID

----------- ---------- -----------

1 2008-01-15 1

2 2008-02-15 1

(2 row(s) affected)

OrderID OrderDate CustomerID

----------- ---------- -----------

1 2008-01-15 1

(1 row(s) affected)

OrderID OrderDate CustomerID

----------- ---------- -----------

2 2008-02-15 1

(1 row(s) affected)

As you can see, our data has been split up into separate tables based on the partitioning column. We can easily create additional tables to partition our data into (for example, an OrderPartitionMar08 table) and then alter our view to union in the additional table. Likewise, we can easily remove a block of data by excluding it from the view and then dropping the table.