Refresh Your Views With sp_refreshview

With SQL Server 2008 R2 having been released it seems like a good time to remind people about the sp_refreshview system stored procedure. How are those two things related? Great question.

When migrating or upgrading to newer versions (such as R2), you need to be aware that a simple restore is not always enough. First, you should be using the upgrade advisor, which will help you find any potential “stub-your-big-toe” problems as I like to call them. After you get the database migrated to the new instance you will want to update all statistics (preferably with FULLSCAN) as well as do a DBCC CHECKDB with DATA_PURITY. For a comprehensive list of all good things related to the upgrade process I will point you to Cindy Gross and the SQLCAT team.

Now, nowhere in that documentation will you find a mention of sp_refreshview. But in my experience it is a good idea to refresh all your views as part of your upgrade process. Why? Great question.

Believe it or not, every now and then someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. The point here is that your view may not be contained in just your database. In what could be the most dramatic twist of all, sometimes these views are created using a SELECT * syntax.

I know, I know…what are the odds that you could have such code in your shop? But it happens. And when you have bad code on top of views that go to other databases (or views of views of views of whatever else some sadistic person built) you are going to want to use sp_refreshview to refresh those views.

So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, much like any movie with Stephen Baldwin. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. It’s like flossing: it doesn’t take much effort, and the end result is usually worth that little effort.