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!