non updatable views sql server

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:

USE TEST;
CREATE VIEW vStud
AS
SELECT DISTINCT Class
FROM Students

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

INSERT INTO vStud(Class)
VALUES('Supreme');

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

Non Updatable Views SQL Server