Modifying Data in Partitioned Views

Yesterday I covered the basics of partitioned views.  I also mentioned there were some gotchas when it comes to modifying the data in those partitioned views.  I'd like to go into more detail about that today.  The first big gotcha on updating data in a partitioned view is making sure you can update the data in that view.  In order for your partitioned view to be update-able you must ensure:

• The view must be composed of SELECT statements combined with the UNION ALL statement.
• Each SELECT references only one base table.

If you've done that, then there are some gotchas when it comes to INSERT, UPDATE, and Delete.

INSERT Gotchas

In order to INSERT data into a partitioned view you must follow the following rules:

• You have to define all columns in your INSERT statement, even if you want to pass a NULL, or a default value.
• You can't use DEFAULT in the VALUES clause.
• All values inserted must pass the CHECK constraints defined on all constrained columns.
• None of your tables can have an IDENTITY property ( auto-increment BAD!)
• No TIMESTAMP columns.
• You can't reference the view itself or any member table in the INSERT statement.  That means if you're going to make sure that row doesn't exist before inserting it... check it in an IF THEN before the INSERT statement.

If you follow these rules, you can INSERT data into your partitioned view.

UPDATE Gotchas

When Updating you have a few more issues to deal with.

• You can't use the DEFAULT keyword as a value in your SET clause, even if the column has a DEFAULT value.  Reference the DEFAULT value explicitly.
• You cannot update a PRIMARY KEY value if the column is TEXT, IMAGE, or NTEXT.  Seriously who would use those as a PRIMARY KEY...seriously?
• Just like inserts, you can't UPDATE if you have a TIMESTAMP column in any of your base tables.
• And just like inserts, you can's reference the view or any of the base tables in your UPDATE statement...Store referenced values in an intermediate variable, table or table variable and you're good to go.

DELETE Gotchas

Finally, the only real gotcha for DELETE statements against a partitioned view is you cannot reference the partition or a base table as a part of the delete statement.  I'm not saying you can't write  a DELETE FROM partitionedViewName... you just can't do a DELETE FROM pvn FROM partitionedViewName INNER JOIN baseTableName on...  you'll get an error.

That's it... All the gotchas when manipulating data in a partitioned view.  If you have any questions, send them in.  I'm here to help!

Partitioned Views

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.