why view is not updated if my table definition is updated?

Question

Suppose i have a table as the definition below:

CREATE TABLE Test
(
    a INT,
    b INT
)

And after that, i am creating a view on the table as,

CREATE VIEW ViewTest
AS
SELECT * FROM Test

After that, when i run the query on view, it returns me with two columns i.e. A & B.

And, later i have updated the definition of table and inserted a new column in it:

ALTER TABLE Test ADD c INT

But, now when i run the view it again returns the view statement, it returns me the same number of columns, now three columns.

I just wanted to know why? Because i have used the Select * statement, so everytime it should return me with the whole of columns.

Answer 1

When you create a view it stores the view definition as metadata in the system tables. Even if you use SELECT * FROM it will store the exact column names e.g. SELECT a, b FROM

If you then update the base table the metadata definition for the view still remains the same so any new columns won't be picked up.

You need to either drop and recreate the view or run sp_refreshview to update the definition

Answer 2

When you add new columns to Test, ViewTest already exists, and only has columns A and B. You'd have to drop ViewTest and re-create it for your "SELECT *" statement to retrieve the new column C.