Refreshing Views

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.

Comic Publisher

Let’s say we have a Comic table and an Author table. For the sake of this example, this is what our Author and Comic tables look like:

Assume there was a requirement to pull that information together in a view. To meet that requirement, a non-schema-bound view was created by running:

CREATE VIEW ComicDetails AS
SELECT c.ComicID, c.Title, a.AuthorFirst, a.AuthorLast, c.Publisher, c.ReleaseDate
FROM Comic c
JOIN Author a ON c.ComicAuthorID = a.AuthorID;
GO

Alter the Column

Here is where things get tricky. Currently, the Publisher column is VARCHAR(8). That’s long enough, assuming our publishers are Marvel and DC. But what happens when we want to store information on comics published by Dark Horse? To account for Dark Horse and publishers with longer names, we’ll alter the column in the Comic table to be VARCHAR(20):

ALTER TABLE Comic
ALTER COLUMN Publisher VARCHAR(20);

This completed successfully since our view was not created WITH SCHEMABINDING. And now we’ll insert our Dark Horse record:

INSERT INTO Comic
VALUES (3, 'Star Wars', 101, 'Dark Horse', '2024-03-13');

Our insert was made successfully. If we refresh in SSMS and check the table, we see our VARCHAR(20).

If we do the same to our view, we still see VARCHAR(8).

What do we need to do so that VARCHAR(20) is displaying accurately in the view?

We need to refresh the view. We can do that by running the aptly named sp_refreshview procedure:

EXEC sp_refreshview 'ComicDetails';

Now we can refresh the view in SSMS and see our view matches for Publisher.

Now That’s Refreshing

If you update table columns, keep in mind that you may need to refresh any related non-schema-bound views.

Thanks for reading!