Why do SQL Server views need refreshing?

Views in SQL are a great way to simplify complex queries, to hide sensitive data, or to provide a different perspective on the data. But they can also be a source of confusion and frustration.

Why do SQL Server views need refreshing?

A view, basically, is a stored query represented as a virtual table. When you create a view, you can use it just like a table which means you can query it, join it, and use it in other queries. Basically, a view is a snapshot of the schema at the time it was created.
However, if a schema change occurs which causes the underlying object to change, the metadata of the view will not be updated automatically and the results of the view will be incorrect. Note that the need to refresh a view depends on the underlying object. For example, if the underlying object is a view, a refresh may not be needed.

Schema changes can be caused by a number of things, such as adding a new column to a table, changing the data type of a column, or even deleting a column. So basically if you have a view that references a table that has changed, you will need to refresh the view.

How to refresh a view

There are a few ways to refresh a view. The most common way is to use the DROP VIEW and CREATE VIEW statements. This will drop the view and then recreate it.

DROP VIEW view_name

CREATE VIEW view_name AS
SELECT column_name
FROM table_name
WHERE condition

This is a simple and effective solution, but it can be time-consuming if the view is complex.

Another way is to use the stored procedure sp_refreshview which is a system stored procedure available in SQL Server.

EXEC sp_refreshview 'view_name'

This stored procedure will refresh the view and update the metadata to match the base table.

Finally, if the tables are all in the same database on the same server you can use the option WITH SCHEMABINDING when creating the view.

CREATE VIEW view_name WITH SCHEMABINDING AS
SELECT column_name
FROM table_name
WHERE condition

This option binds the view to the schema of the underlying table. This means that this base table cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

Comments

[geraldew]

Thanks, as I've recently been tasked to work SQL-Server this will likely be a useful tip.

However, an experience with other SQL engines and dialects is that this kind of refreshing a view by dropping and replacing can fail badly if a table the view depends on happens to temporarily not exist at the time.

Therefore a better sequence is to create a new named view and only if that was successful to then drop the old one and rename the new one to the old name. Of course, if sp_refreshview does exactly that then this suggestion is moot.