Introduction to sp_refreshview?

sp_refreshview is a system procedure used to update the metadata for the specified view those are not schema bounded.
The definition of sp_refreshview is as below:

create procedure sys.sp_refreshview  
    @viewname nvarchar(776)  
as  
 declare @ret int  
 exec @ret = sys.sp_refreshsqlmodule_internal @viewname, N'OBJECT', 1 -- viewonly  
 return @ret 

Why do we need sp_refreshview?

If your underlying object (table/view) has a modification( we will look into the type of modification sometime later), the metadata of the view needs to be updated to take the effect of the changes unless the VIEW has been sepcified with SCHEMABINDING. To update the metadata, we need to either recreate the view(drop+create) OR need to refresh the view with sp_refreshview system procedure.

Lets look at an example.

--create a Base Table
Create Table ParentTable(EmployeeID int, EmployeeName Varchar(50))

Insert into ParentTable Values(1,'Ben'),(2,'Tom')

--create a View

Create View ParentView
as
	Select * From ParentTable
	
Select * From ParentView	

--Make a modification on Table schema
Alter table ParentTable add EmployeeAge int

Select * From ParentView	

Here, we can see that the EmployeeAge is not coming as part of the view as the metadata has not been updated though the table has the column.To update the metadata, we are going to refresh the view using sp_refreshview.

sp_refreshview 'ParentView'

Select * From ParentView	

Now, we can see the EmployeeAge is part of the View ParentView. We are good…

Interesting part…Do we need to refresh view for all modification?

No. Not always. It depends on your underlying object type. If you have a table as an underlying object, yes, you may need to do refresh view for the modifications.But, if the underlying object is again another VIEW, then we may not do a refresh for all cases.

Lets look at an example:

To make the example easier, We are going to create another view CHILDVIEW on PARENTVIEW as underlying object.

Create View ChildView
as
	Select * From ParentView
	
Select * From ChildView	

Let us do a modification on the ParentView by adding a Union All as below.

Alter View ParentView
as
	Select * From ParentTable
	Union All
	Select * From ParentTable

Select * From ChildView	

Here, we can observe that even there is a change in the ParentView,ChildView works as expected without a refresh.

Below, I compiled a small table for various types of changes. Of course,the table does not cover all scenarios. You can test for any specific case and confirm further.

Hope, you enjoy this post, please have your thoughts as part of our learning and sharing…..