I've created a view with CREATE VIEW dbo.myView AS SELECT * FROM dbo.myTable
. myView
does not select new columns when I add columns to dbo.myTable
. Is there a way I can make my view select all columns from a table even after I add columns without having to update the view?

- 16,589
- 8
- 53
- 80
-
@Damien_The_Unbeliever ok, I'll do that. I'm suprised sql-server even allows select * in view definitions then. It's misleading. – Steven Wexler Mar 28 '14 at 15:15
-
Are you sure you got view and the table on the same database. – Hunter Mar 28 '14 at 15:16
1 Answers
No. You have to update the view either by recreating it or using sp_refreshview
.
This is documented in CREATE VIEW
:
If a view is not created with the
SCHEMABINDING
clause,sp_refreshview
should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
And SCHEMABINDING
doesn't help you here either - because that prevents you even making breaking changes to the base tables.
In general, SELECT * ...
is a lazy shorthand and the only place you should be using it is inside IF EXISTS()
tests. Everywhere else, you're basically setting yourself up for poor performance (especially if someone else later comes along and adds a new column to your table containing the complete works of shakespeare for every row).

- 234,701
- 27
- 340
- 448
-
The same issue can occur with inline user-defined functions which use `SELECT *` and there is no easy solution such as `sp_refreshview`. See, e.g. http://bytes.com/topic/sql-server/answers/612591-how-recompile-refresh-udfs and http://www.sqlservercentral.com/Forums/Topic188980-8-1.aspx. – Riley Major Mar 28 '14 at 17:18