8

During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views.

Where there/Are there significant performance implications to doing this? Or is this more a case of senior developers/DBAs' telling junior personnel not to do this because they can unwittingly wreak havoc with an incorrect join.

EDIT
I'm using MSSQL 2000-2008 (Depending on the particulars of the client)

6
  • Which DBMS are you using?
    – user1822
    Dec 28, 2011 at 12:53
  • @a_horse_with_no_name Updated to indicate DBMS. I was hoping that the answer would be fairly generic across all DBMSs'. Was I being naive?
    – Tim Sparg
    Dec 28, 2011 at 13:01
  • Are you talking about views that are naturally updatable or views that you would have to write an INSTEAD OF trigger for? I've not heard this advice particularly BTW were you never given a reason? Dec 28, 2011 at 13:03
  • @TimSparg: it depends on how the view is made updatable. If instead of triggers are used, then there is no ambiguity and I don't see a reason not to use them. But I'm not that familiar with SQL Server so I can't comment on using views without a trigger.
    – user1822
    Dec 28, 2011 at 13:05
  • @Martin Smith I'm talking about naturally updatable views. I was vaguely told that there are pitfalls and performance issues, this was then reinforced at my workplace were we where firmly told not to update through views. I'm starting to think that it was just something the senior Devs/DBAs would say because they didn't feel like explaining to be careful about updating through views (IE inner vs outer join)
    – Tim Sparg
    Dec 28, 2011 at 13:23

1 Answer 1

11

For a view to be updatable without using INSTEAD OF triggers "SQL Server must be able to unambiguously trace modifications from the view definition to one base table.".

There is no performance disadvantage to Updating these Views as SQL Server will just generate a query plan for the base table affected. One possible disadvantage might be that it adds a layer of obfuscation so unless you are using Views as a security layer it is clearer to just write code that updates the base table directly.

Another one might be if the View contains a one to many JOIN and you update the "one" side with a value from the "many" side it is undeterministic what result you end up with but the same applies to SQL Server's proprietary UPDATE ... FROM syntax. You would need to use MERGE or a scalar correlated sub query to avoid this possible issue.

For Views that are not updatable and require an INSTEAD OF trigger there are performance implications as the inserted and deleted pseudo tables need to be generated from the base table so if possible updating the base tables directly will likely be more efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.