Press "Enter" to skip to content

Refreshing a View in SQL Server

Chad Callihan hits the reset button:

I recently encountered a question related to views: what happens when you make a change to the table that a view is based on? For example, if you change a column from VARCHAR(8) to VARCHAR(20), how does the view handle that change? You might expect the view to update, but it won’t do it on its own. You have to refresh the view.

Let’s look at a mocked up example.

Click through for that example. You’d think it could do so on its own, but nope. I suppose the reason you can’t is probably related to linked server and external object references in views, where that remote resource can change schema and SQL Server wouldn’t know about it. Perhaps also the idea that a person may be authorized to change a table in one database or schema but shouldn’t be authorized to modify (even to refresh?) a particular view that references the table.