.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
Advertisements

About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- https://www.youtube.com/watch?v=Lp7nSImO5vk Learn AngularJS Step by Step:- https://www.youtube.com/watch?v=0kmdjqgO9IY Learn Design Pattern in 8 hours:- https://www.youtube.com/watch?v=YDobmucohqk Learn C# and .NET in 60 days:- https://www.youtube.com/watch?v=yh2SrzCkNQA Learn MSBI in 32 hours:- https://www.youtube.com/watch?v=mGPJx3ocFgg Learn SharePoint Step by Step in 8 hours:- https://youtu.be/C2fW76SwJNU
This entry was posted in Uncategorized and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s