My view isn’t reflecting changes I’ve made to the underlying tables.
8January 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
SQL Studies 
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
I will use this, thanks…
EXEC sp_RefreshView myTable
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 :).
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.
[…] Your data may move around unexpectedly. […]
[…] if you aren’t aware of what sp_refreshview is you can read a bit more about it here. But basically it’s exactly what it sounds like. It refreshes the view […]
[…] One of the only good things to come out of all of the lockdowns this last year is the opportunity to attend presentations at user groups that aren’t in your area without traveling. So recently I “went” to Stored Procedure Optimization Techniques presented by Kimberly Tripp (blog|twitter) at the Ohio North Database Training group. It was a great session and at one point she mentioned using sp_recompile. Which in case you didn’t know can be run against a table. Obviously a table doesn’t get compiled, but when you do this every stored procedure associated with the table is marked for recompile. Now, another fact you may not know, if you put SELECT * in a view, and change any of the tables associated with that * the changes won’t take effect until you run sp_refreshview. If you want some detail I wrote about this here. […]