Look at the below statement.
CREATE VIEW vwTest
AS
SELECT * FROM TestTable;
A simple statement to create a View, right?
Assume that “TestTable” has just three columns on which a View is built. What happens if we add one more column to the base table, i.e. TestTable? Is it feasible that the newly inserted column will appear in the view? Will changes to the database schema affect the View definition?
The answer is, No.
Let’s check it out.
Creating a table
CREATE TABLE TestTable (id INT,col1 STRING, col2 STRING);
Inserting few rows
INSERT INTO TestTable VALUES (1,’text1′,’data1′), (2,’text2′,’data2′);
Creating a view
CREATE VIEW vwTest AS SELECT * FROM TestTable;
Altering table definition
ALTER TABLE TestTable ADD COLUMNS (col3 STRING);
Check the view definition and data.
DESC vwTest;
DESC TestTable;
So, modifications to the table’s definition will have no effect on Views. Hence, an ALTER VIEW statement must be issued in order for the table definition changes to be reflected in the View.
Altering the view definition.
ALTER VIEW vwTest AS SELECT * FROM TestTable;
Verification:
DESC vwTest;
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 