Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




.NET and SQL Server interview questions -Show Practically 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


 

Share this article   |    Print    |    Article read by 1439 times
Author:
Shivprasad koirala Koirala
I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small E-learning company in India. We are very much active in making training videos , writing books and corporate trainings. Do visit my site http://www.questpond.com for .NET, C# , design pattern , WCF , Silverlight , LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server training and Interview questions and answers
Related Articles: No related article
Related Interview Questions: No related interview question