Mark Wilson
asked on
SLQ View not updating
Hi
SQL 2014
I have created a simple view
CREATE view v_example
as
select *
from table
When I add another column to the table in never adds the column to the view - I am always having to drop and recreate it or run sp_refreshview
Is there anything I can add to the view syntax to make sure any changes to the table i.e. columns are updated automatically?
Thanks
Mark
SQL 2014
I have created a simple view
CREATE view v_example
as
select *
from table
When I add another column to the table in never adds the column to the view - I am always having to drop and recreate it or run sp_refreshview
Is there anything I can add to the view syntax to make sure any changes to the table i.e. columns are updated automatically?
Thanks
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am not sure what are you after but why would you create a view like that and not use directly the table:
select *
from table
instead of
select *
from view
If you use the table you don't need to refresh anything. If this was just for exemplification then OK.
select *
from table
instead of
select *
from view
If you use the table you don't need to refresh anything. If this was just for exemplification then OK.
You could force yourself to drop the view before modifying the original table by specifying WITH SCHEMABINDING when you create the view. You just re-create the view after modifying the underlying table(s):
CREATE view v_example
WITH SCHEMABINDING
as
select *
from table
CREATE view v_example
WITH SCHEMABINDING
as
select *
from table
Scott, I think the author is trying to avoid the View recreation.
Point is that god practice recommends to NOT use * as the columns list in a view. Another thing is that if you expect that a table will have its structure changed often then there is definitely something wrong with the design. Adding columns to a table should be an exceptional event that should not create concern when it comes to the views it is part of refresh.
@Vitor:
You can't avoid it, that's that point. "*" in a view is interpreted when the view is created, not dynamically every time the view is used, because that would be too much overhead.
You can't avoid it, that's that point. "*" in a view is interpreted when the view is created, not dynamically every time the view is used, because that would be too much overhead.
You can't avoid it, that's that point.Correct. That's what we are trying to say to the author :)
Mark, what do you want to do with this question?
This is one of the cases where using SELECT * in the views may actually case visible harm. Once the view refreshes, the new column will start coming in the result set and if the calling application is unable to handle more columns than coded for, it will break. Hence, the best practice is to always specify the column list in the SELECT statement and explicitly update the views once the application has been upgraded to handle the new column.
In case you are in the midst of a major change across tables, the MSDN article (https://msdn.microsoft.com/en-us/library/ms187821.aspx) provides a script to update all the views referencing a particular object/table so that you do not have to write an explicit sp_refreshview call.
Also, note that sp_refreshview will only work for non-schema bound views.