View not update after source table changed

Lora 100 Reputation points

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,552 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 16,851 Reputation points Microsoft Vendor

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 35,846 Reputation points

    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.

    0 comments No comments