Views not changing when table changes

Question

Views not changing when table changes

tshad

SSCertifiable

Points: 5905
More actions
August 28, 2016 at 1:56 am

#320931

I have the following view, I get 10 columns back. If I add a new column to the "dbo.Jobs" table, I still get 10 columns back and the new column is not there.
ALTER VIEW [dbo].[vwGetJobs]
AS
SELECT * FROM dbo.Jobs
GO
When I add a column to the table, shouldn't the "SELECT *" come back with all the columns in the table?
Thanks,
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

when the view is actually compiled, the wild card is expanded to the list of actual column names in the compiled version of the view;that's to avoid dynamically having to check the view definition is valid each time it is called.

that's what you are seeing.

if you modify an underlying table used by the view, there's a built in function you can call to help with that;

exec sp_refreshview viewname

for views that do SELECT *, it will expand column names again and recompile it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

theres some thoughts here

https://coding.abel.nu/2013/01/avoid-broken-views-in-sql-server-with-schemabinding/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

This is why one must be very careful with the SELECT * approach. Since you will have to run a refresh of the view, I find it less potentially confusing to simply list out the column names in the Select clause. Any column addition means you add your new column to the view definition with no fancy extra steps to consider (like whats that refresh stored proc again?). A simple straight forward approach instead.

I think there is the temptation to shirk here when the number of columns to list out is great. There are add on tools that can help in this regard. In fact, if you just drag over the columns folder from the object explorer window in SSMS, it will spell all of these out for you.

----------------------------------------------------