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