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

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “Non Updatable Views SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *