Skip to content

Modifying Data in Partitioned Views

2010 August 25
tags:
by Shannon Lowder

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!

2 Responses leave one →
  1. April 10, 2013

    Hi! This is my 1st comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading through your articles. Can you suggest any other blogs/websites/forums that deal with the same topics? Appreciate it!

    • June 27, 2013

      There are many other blogs out there. I’d start with SQLServerPedia, or one of the bigger collections. You’ll start seeing articles from many different authors, find the ones that really explain things the way you need to see them. Then you can follow links to their personal blogs. That’s how I got started following others. It really introduces you to many different views of SQL Server!

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS