View not update after source table changed

Lora 200 Reputation points
2023-11-10T03:18:27.24+00:00

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.
14,016 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 28,121 Reputation points
    2023-11-10T03:33:20+00:00

    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 45,121 Reputation points
    2023-11-10T06:02:10.5733333+00:00

    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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.