Hello Folks,

You might have heard about this, but are really aware of this type of views which are not updateable?

If not, then I am going to give you some heads up, so please go through it carefully:

  • Non-Updateable Views may affect INSERT, UPDATE, and DELETE operations. 
  • One of the main reasons, why the views become non-updateable is because of inclusion of aggregate functions (which also includes DISTINCT), Group By, and Join. 
  • Also in the cases of Nested Views, which includes those views that is non-updateable, will cause the final view also to be non-updateable. 
  • This will become more clear to you, if you see further; 

As shown below, I have used a table called Students from “TEST” database;

1_SQL_Server_Non_Updateable_Views

Now, we will create a view name “vStud” which will also include aggregate function like DISTINCT, and see what happens:

Therefore, to test the updateability of the view, the next query attempts to perform an INSERT command through the view:

Now, instead of result an error arouses;

Msg 4403, Level 16, State 1, Line 1

Cannot update the view or function ‘vStud’ because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

Hence, it would be better that if you would avoid using aggregate functions inside the view.

This was all about Non-Updateable View.

Hope you like it :)

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook