View not update after source table changed

Question

I created a view with definition:

select * from Mytable

Then I add one more column to Mytable. However, the view did not display the new column. Although * means the whole columns.

Why view is not updated when the table modified?

Answer 1

Hi @Lora

Refer to this doc CREATE VIEW:

If a view is not created with the SCHEMABINDING clause, run sp_refreshview when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

To recompile, you can use alter view or sp_refreshview

To avoid table definition changed, you could use SCHEMABINDING so any changes to the underlying tables need to be done deliberately.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Although * means the whole columns.

Yes, at compile time of the view. If you modify the base table(s), you have to recompile the related views.