My view isn’t reflecting changes I’ve made to the underlying tables.

5

January 20, 2013 by Kenneth Fisher

Problem: You’ve added columns to the base table of one of your views, but the view isn’t reflecting the change.

Over the years I’ve seen lot’s of views created similar to this one.

CREATE VIEW vw_TableView AS
	SELECT * FROM TableName

Generally the argument is that if I put “SELECT *” rather than an explicit field list, then when my table changes so will my view. Unfortunately it doesn’t work that way.

Let’s try an example.

Create a test table and populate it with some data.

 CREATE TABLE TableName (Column1 varchar(10))
GO

INSERT INTO TableName VALUES ('abcdefg')
INSERT INTO TableName VALUES ('hij')
INSERT INTO TableName VALUES ('klmnop')
INSERT INTO TableName VALUES ('qrstuvwxy')
INSERT INTO TableName VALUES ('zabcde')
INSERT INTO TableName VALUES ('123456')
GO

Create a test view.

 CREATE VIEW vw_TableView AS
	SELECT * FROM TableName
GO

Test the view to make sure we are getting the data we expect.

 SELECT * FROM vw_TableView
GO

So far so good. The output is exactly what we expected. Now let’s add a column to the table and populate it.

 ALTER TABLE TableName ADD Column2 INT
GO
UPDATE TableName SET Column2 = 3
GO

And try out the view again.

 SELECT * FROM vw_TableView
GO
Column1
abcdefg
hij
klmnop
qrstuvwxy
zabcde
123456

Now wait just a minute. The output I’m getting looks exactly like it did before I added Column2. All I’m seeing is Column1. Now the first thing I do when debugging something like this is make sure the view should in fact be pulling the new column. So:

EXEC sp_helptext vw_TableView
 Text
---------------------------------------------------------------

CREATE VIEW vw_TableView AS
	SELECT * FROM TableName

Ok, so the code still looks correct. So why aren’t we pulling all of the columns even though we are using a *? From what I understand the metadata for the view is not automatically updated when the tables are modified.

The fix is to either drop and re-create or alter the view or to use the sp_refreshview stored procedure. Sp_refreshview has the combined benefit of being the simplest method and not messing up any explicit permissions on the view caused by dropping it.

 EXEC sp_RefreshView vw_TableView
GO

And test the view again.

 SELECT * FROM vw_TableView
GO
Column1 Column2
abcdefg 3
hij 3
klmnop 3
qrstuvwxy 3
zabcde 3
123456 3

And now we have the correct number of columns for our view.
Next let’s try going the other way. We remove a column from the table.

 ALTER TABLE TableName DROP Column2
GO

And we try querying the view again. (I’m hoping no one expects it to work correctly.)

 SELECT * FROM vw_TableView
GO

This time we get an error.

 Msg 4502, Level 16, State 1, Line 1
View or function 'vw_TableView' has more column names specified than columns defined.

If we again run sp_refreshview then the view will once again show the expected data.

 EXEC sp_RefreshView vw_TableView
GO
SELECT * FROM vw_TableView
GO
Column1
abcdefg
hij
klmnop
qrstuvwxy
zabcde
123456

And last but not least some cleanup code.

DROP VIEW vw_TableView
DROP TABLE TableName
GO
About these ads

5 thoughts on “My view isn’t reflecting changes I’ve made to the underlying tables.

  1. christi parks says:

    I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
    And has anyone studied from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
    would really appreciate help

  2. Siddarth says:

    I will use this, thanks…

    EXEC sp_RefreshView myTable

  3. Brad says:

    You need to be VERY careful when using “select * ” in any views within SQL Server and i would recommend that you do not use “select *” in any views. The reason being in certain situations the view will actually shift the data and put under the wrong column heading if you modify the underlying table if you do not remember to explicitly refresh the view after modifying the underlying table. Explicitly list all column names in your view instead of using select *.

    Here is a script that demonstrates this issue, in the 2nd result set the userfirstname and userlastname columns in the view return the WRONG VALUES:

    –CREATE BASE TABLES AND VIEW
    CREATE TABLE testUserTbl(userid int not null primary key identity, userfirstname varchar(128), userlastname varchar(128))
    go
    CREATE TABLE testUserLogons(logonid int not null primary key identity, userid int foreign key references testUserTbl(userid), logonTime datetime, logonPage varchar(256))
    go
    CREATE VIEW vw_UserLogons as select l.*, u.userfirstname, u.userlastname from testUserLogons l inner join testUserTbl u on l.userid = u.userid
    go

    –INSERT DATA
    INSERT INTO testUserTbl(userfirstname, userlastname) VALUES(‘First1′,’Last1′);
    INSERT INTO testUserTbl(userfirstname, userlastname) VALUES(‘First2′,’Last2′);
    INSERT INTO testUserLogons (userid, logonTime, logonPage) select userid, getdate(), ‘testpage.aspx’ from testUserTbl;
    INSERT INTO testUserLogons (userid, logonTime, logonPage) select userid, getdate(), ‘testpage2.aspx’ from testUserTbl;
    go

    –VIEW RESULTS BEFORE MODIFYING TABLE: testUserLogons
    select * from vw_UserLogons;
    go

    –ADD NEW COLUMN
    ALTER TABLE testUserLogons ADD queryString varchar(256);
    go

    –POPULATE DATA IN NEW COLUMN
    update testUserLogons set queryString = ‘?x=1′;
    go

    –VIEW RESULTS AFTER MODIFYING TABLE: testUserLogons
    select * from vw_UserLogons;
    go

    –CLEANUP THE VIEW/TABLES THAT WERE CREATED
    if object_id(‘vw_UserLogons’) is not null
    drop view vw_UserLogons;
    go
    if object_id(‘testUserLogons’) is not null
    drop table testUserLogons;
    go
    if object_id(‘testUserTbl’) is not null
    drop table testUserTbl;
    go

    • Excellent point and I should have put that example in my original post. Basically it’s the same problem, where the metadata for the view hasn’t been updated after the tables have been altered. The only difference is that it’s MUCH harder to notice and potentially more damaging. I’ve seen it happen a number of times. Imagine an insurance system where insurance expiration date and date of death get switched. sp_refreshview will correct the problem, but that assumes you notice it. I also agree that I wouldn’t recommend that anyone use a SELECT * in a view, unfortunately I (and probably most others, yourself include I’m sure) deal with a fair amount of legacy code that is already using it. Again good point and thanks for adding the additional info :).

  4. Parker says:

    Good tips. I agree with Brad, you should be explicit in your columns, which avoids the issues. However, in the real world you’ll always have someone, maybe even yourself, who has done a select * and will do it again. Which makes this post helpful. I’d also add that you could just iterate through all queries using sysobjects where type ‘v’ and run the refreshview against each, and have this run routinely, and/or manually as needed. Then you don’t have to wonder if any other queries are broke, just refresh them all in one swift kick.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 173 other followers

%d bloggers like this: