Last week I covered partitioning.  I explained how you could set up filegroups in your database, and then split data from a single table or index across those filegroups.  That way you can reduce blocking in your objects by physically separating the data into parts.  I even covered how you could use partitioning to speed archiving older data from production tables using SWITCH.  This week I want to dive into partitioned views.

Like partitioning tables and indexes across multiple filegroups, you can set up multiple tables and then reference them through a single view, making it appear you have one large table.  These tables can exist all on the same server, or on several servers.  When you start using tables on other servers in your view it’s referred to as a distributed partitioned view.  To set up distributed views, you’re going to need to learn about Federated Database Servers.

I would like to point out using partitioned views is not the preferred method, but there can be times where it will be your only choice.  That’s why I’m teaching you this topic.

Creating Partitioned Views

The data in the tables referenced by your partitioned view should be split into ranges of data values based on on of the columns common to all the member tables.  Each of these tables must be defined in a CHECK constraint specified on that partitioning column.  That way, when you execute a query against the partitioned view, the query optimizer will use that constraint to determine which member tables to hit in order to find the data you’ve requested.

Let’s set up some demo tables in our KOTOR database.  This time we’re going to track sales of our items.  In our example, we have sales tables that will be split by year, then combine them into one partitioned view.

CREATE TABLE sales_2010 (
    saleID INT
  , customerID INT
  , saleDate DATETIME
      CHECK(DATEPART(yy, 2010)
  , saleYear INT
      CHECK( saleyear = 2010)
  , saleTotal DECIMAL(9,2)
    CONSTRAINT PK_Sales2010__saleID_saleYear PRIMARY KEY(saleID, saleYear)
)

This is our template, we will also create tables for sales_2009, sales_2008, and sales_2007, the only thing we have to change will be the name, and the year in the CHECK constraints.

Once we have those defined, creating the VIEW is simple.

CREATE VIEW total_sales (
  SELECT saleID, customerID, saleDate, saleTotal FROM sales_2010
  UNION ALL
  SELECT saleID, customerID, saleDate, saleTotal FROM sales_2009
  UNION ALL
  SELECT saleID, customerID, saleDate, saleTotal FROM sales_2008
  UNION ALL
  SELECT saleID, customerID, saleDate, saleTotal FROM sales_2007
)

Using Partitioned Views

Now that we have defined the total_sales view, you can use it like any other view. You can SELECT from it, and you can update it.

There are two ways you can update a partitioned view. If you have partitioned your tables using the primary key, then you can update the view directly. (UPDATE viewname, SET column = value). But if you didn’t use the partitioning column as part of your primary key, then you’ll have to create an INSTEAD OF INSERT and UPDATE triggers for your view. If you go this route, make sure you build in some error handling to prevent duplicate rows from being inserted.

And to be honest, that can be a pain. Make it easier on yourself, and develop your partitioned views to segment the data based at least in part on the tables’ primary key!

Implementing partitioned views can make it easy to archive data. You can create a new table to cover the new segment of data, then update the view to reference the new table, and remove the reference to the old data.  For those of you not as familiar with partitioning tables, that may be a way of learning to become more familiar with partitioning… just sayin’.

Next time, I’d like to cover some of the gotchas that can exist when you go to running INSERT, UPDATE, and DELETE against your partitioned view.  For now, let this sink in, and play around with the basics.  If you have any questions, send them in!  That’s what I’m here for.