Solved

SLQ View not updating

Posted on 2016-10-18
10
68 Views
Last Modified: 2016-11-19
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
0
Comment
Question by:halifaxman
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41848076
Is there anything I can add to the view syntax to make sure any changes to the table i.e. columns are updated automatically?
No. When you create a view the metadata information is stored and can only be updated by the sp_refreshview or an object recreation.
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41848081
This is by design of Microsoft SQL Server so that applications  and reports that depend upon the columns in the view do not break.

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.
1
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41848298
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41848584
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
1
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41849533
Scott, I think the author is trying to avoid the View recreation.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41850024
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41850459
@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.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41851552
You can't avoid it, that's that point.
Correct. That's what we are trying to say to the author :)
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41871714
Mark, what do you want to do with this question?
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Insert parts by customer 12 35
Inserting oldest record into new table. 5 25
How to use three values with DATEDIFF 3 27
SQL Function NOT ROUND 9 11
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question