.NET and SQL Server interview questions - Can you describe Sql Server Views are updatable?

Answer:

View is a virtual table, which can contains data (rows with columns) from one or more table and you can retrieve data from a view.

Let’s demonstrate a simple example in which we will see that how to create a view on single table and will also see that if we update the view the respective table on which the view is created is updated or not.


Now let first see how to create view.

Go to View folder in SQL Server > Right click on it > select New View.

As soon as you click on New View, the following window will appear like below.

Now, just select the table name from the list on which you wish to create a View and Click on Add then click on close. Once you click on close a new window will appear which allow you to create View on the respective column.



After selecting the column name just save the view and give View a nice name.


Once you have completed the above step you will see that the respective View is added in the View folder.


Now let’s see that when we update the view the respective table is also updated or not.

Query:-

Update [Practice].[dbo].[Cust_View] set Customer_Contact = 96641122 where
Customer_Name = 'Feroz'

Now just go to the table on which the view was created and check whether the table is updated or not, you will see that the table is also updated when you update the View.
Now let’s create a view based on two tables and try to update a view.

create view View_Cust as SELECT    dbo.Customer.Customer_Name, dbo.Customer.
Customer_Contact,dbo.[Order].Product_Name,dbo.[Order].Price
FROM dbo.Customer
INNER JOIN dbo.[Order] ON dbo.Customer.Order_ID = dbo.[Order].Order_ID

Let’s try to Update View:


Query:-

Update [Practice].[dbo].[View_Cust] set Customer_Contact = 098767554,
Price = 4000 where Customer_Name = 'Feroz'

As you can see in the above query, I am trying to update a column from the Product table and another column from the Order table and try to execute the query the compiler will throw the below error.

Error Message:- View or function 'Practice.dbo.View_Cust' is not updatable because the modification affects multiple base tables.

This means that when you try to update both the table’s column from the view then it is not allowed but you can update single table column.

Please click here to see more .NET and SQL Server interview questions
Regards,

Visit Authors blog for more .NET and SQL Server interview Question