Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite
1

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)

share|improve this question
Which DBMS are you using? – a_horse_with_no_name Dec 28 '11 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 '11 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? – Martin Smith Dec 28 '11 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. – a_horse_with_no_name Dec 28 '11 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 '11 at 13:23
show 1 more comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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