SQL View Remains After Modifying Table

SQL views protect your base table data. So when you create a view, only customized-form of columns only everyone can see. The aim of this post is, will the View still present after the base table deleted or modified? The answer is ‘Yes’. Let us see how.

How Many Types of Views Present in SQL

Generally, views are three types. There is a fourth view called Materialized view. But this is not popular in development.

SQL Views After Modifying Base Table
SQL views still be present in the database even after modifying or delete the base table. So, usually in the production base tables will not be deleted. Just they will be modified.

Let me share the best example on how to create an Aggregated view. Then I will modify the base-table. We will see what may happen.

SQL Query to Create Aggregate View

CREATE VIEW Aggreg_Order_v AS
SELECT Customer_Number
,COUNT(Order_Total) AS Order_Cnt
,SUM(Order_Total) AS Order_Sum
,AVG(Order_Total) AS Order_Avg
FROM Order_Table
GROUP BY Customer_Number ;

The view name is “Aggreg_Order_v. This is now created from ‘Order_table’, which is called base table.

I am now going to ALTER the base table.

ALTER Table order_table
add mgr_no integer;

Now I added a new column (mgr_no) to Order_table. Even after modifying the base table, the view will still present. Unless you drop the view, it is always there.

Subscribe to get access

Read more of this content when you subscribe today.

Interview Questions

  1. Can we delete base-table when view is created on it. – Yes and you will get error while accessing the View.
  2. Can we modify base table when view already created on it – Yes


Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.