SQL Server Views … gotcha while using sp_refreshview

Last week I blogged about use of Views in SQL Server. We also saw different types of view and best practices for designing new view and limitations that may prevent us creating a view too. As a part of best practice while designing we should always include keyword, WITH SCHEMABINDING which prevents any accidental changes to base table on which we have defined the view.

Today’s post is about case when we have not use SCHEMABINDING with view, and in that case if we change underlying structure it will not appear in view till we use system stored procedure called SP_REFRESHVIEW. The syntax is something like this,

-- Sudo Code
sp_refreshview [ @viewname= ] 'viewname'

Snippet 1

USE master
CREATE DATABASE wxy    
GO

-- Creating Sample DB to work with
USE wxy
go

-- Creating Sample Table 1
SELECT  p.BusinessEntityID,
        p.FirstName,
        p.LastName,
        a.AddressLine1,
        a.City,
        sp.Name [State/Province],
        cr.Name [Country],
        a.PostalCode
INTO    wxy.dbo.foo1
FROM    AdventureWorks2008.Person.Person p
        JOIN AdventureWorks2008.Person.Address a ON p.BusinessEntityID = a.AddressID
        JOIN AdventureWorks2008.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID
        JOIN AdventureWorks2008.Person.CountryRegion cr ON sp.CountryRegionCode = cr.CountryRegionCode
GO

-- Creating Sample Table 2
SELECT  p.BusinessEntityID,
        p.FirstName,
        p.LastName,
        a.AddressLine1,
        a.City,
        sp.Name [State/Province],
        cr.Name [Country],
        a.PostalCode
INTO    wxy.dbo.foo2
FROM    AdventureWorks2008.Person.Person p
        JOIN AdventureWorks2008.Person.Address a ON p.BusinessEntityID = a.AddressID
        JOIN AdventureWorks2008.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID
        JOIN AdventureWorks2008.Person.CountryRegion cr ON sp.CountryRegionCode = cr.CountryRegionCode
GO

-- Creating Sample Table 3
SELECT  p.BusinessEntityID,
        p.FirstName,
        p.LastName,
        a.AddressLine1,
        a.City,
        sp.Name [State/Province],
        cr.Name [Country],
        a.PostalCode
INTO    wxy.dbo.foo3
FROM    AdventureWorks2008.Person.Person p
        JOIN AdventureWorks2008.Person.Address a ON p.BusinessEntityID = a.AddressID
        JOIN AdventureWorks2008.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID
        JOIN AdventureWorks2008.Person.CountryRegion cr ON sp.CountryRegionCode = cr.CountryRegionCode
GO

-- Creating View 1
CREATE VIEW dbo.viewFoo1 WITH SCHEMABINDING
AS  SELECT  FirstName,
            LastName,
            City,
            [STATE/Province],
            Country
    FROM    dbo.foo1
go

-- Creating View 2
CREATE VIEW dbo.viewFoo2
AS  SELECT  FirstName,
            LastName,
            City,
            [STATE/Province],
            Country
    FROM    dbo.foo2
go

-- Creating View 3
CREATE VIEW dbo.viewFoo3
AS  SELECT  *
    FROM    dbo.foo3
go


-- Test Run
SELECT  *
FROM    dbo.viewFoo1
go
SELECT  *
FROM    dbo.viewFoo2
go
SELECT  *
FROM    dbo.viewFoo3
go

-- Altering Underlying Structure
ALTER TABLE foo1 DROP COLUMN Country
GO

ALTER TABLE foo2 DROP COLUMN Country
GO

ALTER TABLE foo3 DROP COLUMN Country
GO

-- Refreshing for View 3 will work while for View 2 it will generate error
EXECUTE sp_refreshview 'dbo.viewFoo2'
EXECUTE sp_refreshview 'dbo.viewFoo3'


-- House Cleaning

DROP VIEW dbo.viewFoo1
GO
DROP VIEW dbo.viewFoo2
GO
DROP VIEW dbo.viewFoo3
GO
DROP TABLE dbo.foo1
GO
DROP TABLE dbo.foo2
GO
DROP TABLE dbo.foo3
GO
DROP DATABASE wxy
GO

If you execute above snippet, you will get an error when you will try to remove column from first view, viewFoo1 because we are using SCHEMABINDING which will prevent any changes to underlying structure.

Views 2 and 3 … viewFoo2 and viewFoo3 still will let us drop column because we are not using SCHEMABINDING. But again when we will try to refresh the view 2 we will be greeted with error because we are specifying columns in its definition rather then simply using SELECT * … but we can still can refresh view 3 by using SP_REFRESHVIEW without any error.

Snippet 2

USE AdventureWorks2008
GO
-- Create First View

CREATE VIEW dbo.fooView1 AS
SELECT p.BusinessEntityID,p.FirstName,p.LastName,a.AddressLine1,a.City,sp.Name [State/Province],
cr.Name [Country],a.PostalCode FROM AdventureWorks2008.Person.Person p 
JOIN AdventureWorks2008.Person.Address a ON p.BusinessEntityID=a.AddressID
JOIN AdventureWorks2008.Person.StateProvince sp ON a.StateProvinceID=sp.StateProvinceID
JOIN AdventureWorks2008.Person.CountryRegion cr ON sp.CountryRegionCode=cr.CountryRegionCode
GO

-- Create Second View

CREATE VIEW dbo.fooView2 AS
SELECT * FROM dbo.fooView1
GO

-- Alter First View
ALTER VIEW dbo.fooView1 AS
SELECT p.BusinessEntityID,p.FirstName,p.LastName,a.AddressLine1,a.City 
FROM AdventureWorks2008.Person.Person p 
JOIN AdventureWorks2008.Person.Address a ON p.BusinessEntityID=a.AddressID
GO

-- Run the Second View
SELECT * FROM dbo.fooView2
GO

-- Refresh Second View 
EXEC sys.sp_refreshview 'dbo.fooView2'
GO

-- Re run the Second View
SELECT * FROM dbo.fooView2
GO

-- House Cleaning
DROP VIEW dbo.fooView1
GO
DROP VIEW dbo.fooView2
GO

In this snippet, view 2 … fooView2 is based on fooView1 (NOTE: in real life this is really really bad practice and in general it is not recommended to create a view that is based on another view. It can cause maintenance or alteration nightmares … but here for sake of understanding the concept we are using this example). So, now when we change definition of view1, if we execute just view 2 without using SP_REFRESHVIEW you will see error something like this screenshot.

But if refresh the view using SP_REFRESHVIEW it will work just fine.

If you already noticed … this SP_REFRESHVIEW is really a good help ONLY IF YOU HAVE USED SELECT * IN VIEW DEFINATION rather then STATING INDIVIDUAL COLUMN NAME. So this is kind of contradicting stuff … as a best practice you should always try to avoid using SELECT * , but if you are intend to use SP_REFRESHVIEW then you may have only that option to create view !!!

So, I would still take side of best practice that you should in general avoid creating view by simply SELECT * … but if you have view with such a definition then it’s your lucky day … you can use SP_REFRESHVIEW without any troubles …

That’s it for now …

It’s Just a Thought …