Sql-server – why are people advised not to update through views

sql-server-2008view

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)

Best Answer

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.

Related Question