Why view is not updated if my table definition is updated?
sql, sql-server, sql-server-2008
Solution
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
Problem
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 update the definition of table and insert 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, not three columns. I just wanted to know why? Because I have used the Select * statement, so every time it should return me with the whole of columns.