Are Views automatically updated
SqlViewSql Problem Overview
If I JOIN or CROSS APPLY two tables and create a VIEW, will the view automatically gets updated when I update either of the two tables or I add records to either of them?
Will these new records show up in the VIEW?
Sql Solutions
Solution 1 - Sql
Yes, they are updated, every time you use them.
I think Microsoft sums up what a View is quite clearly:
> A view can be thought of as either a virtual table or a stored query.
http://msdn.microsoft.com/en-us/library/aa214068%28v=sql.80%29.aspx
Views are not automatically cached.
When you SELECT from a view, the database has to run the query stored in the view to get the result set to use in your statement
The data you 'see' in a view, is not actually stored anywhere, and is generated from the tables on the fly.
Because of this be careful running views which are very complex. Always take into account that the view will have to be executed before its result set is accessed.
Solution 2 - Sql
A view is basically a stored query, it holds no data so no, it won't get updated when the tables it's built on are. However as soon as you reference the view the query it's based on will run, so you will see the changes made to the base tables.
Solution 3 - Sql
Yes, a view is a SELECT query against underlying tables/views. If you modify data in the underlying table(s), and if that range is included in the view definition then you will see the modified data.
Solution 4 - Sql
Yes, records will be updated every time.
But if you modify table definition.
Don't forget to refresh view.
> exec sp_refreshview @viewname
Don't use SELECT * in view definition, instead use column name
Solution 5 - Sql
Just adding on to @Curt's Answer, if the update you made to underlying tables is adding or deleting Data, then the view is auto updated with the new data.
If you add or delete the columns form the underlying tables(basically the definition of the View ), then you need to run sp_RefreshView stored procedure to reflect the new schema in your view.
Solution 6 - Sql
Short Answer
Yes, if you query a View it will reflect the changed data in the tables it is based on.
Long Answer
Preface
I read these answers and it made me question how Views work so I did some research and what I found support but also added to the answers listed so I want to add this to the pot.
I source my references with *# which definitions at the bottom.
Overview
There are different types of Views and they have different types of behaviors. Some are stored then updated frequently and others are not stored at all and computed on the fly.
Definition of a View
"A view is a virtual table whose contents are defined by a query...Unless indexed, a view does not exist as a stored set of data values in a database." *1
Nonindexed Views
"Unlike permanent tables, a view has no physical representation of its data unless you create an index on it. Whenever you issue a query on a nonindexed view, SQL Server in practice has to access the underlying tables. Unless specified otherwise..." *1
So Nonindexed Views are calculated when called.
Indexed Views
"An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table." *2
As Indexed Views are stored they are not well suited for tables that often update as they will need to constantly update the materialized data and their index.
Answer
In both cases, Indexed or Nonindexed Views reflect the changes in the tables they refer once the change is made either when you call the View or when the change is made based on if it is Indexed.
References
*1 Inside Microsoft SQL Server 2008 T-SQL Programming Published By Microsoft Press Copyright 2010
*2 https://docs.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15
Solution 7 - Sql
Phtttt for what its worth 7 years later I went with what Neville Kuyt Oct 13 '11 at 14:07 recommended.
Some silly interview moments ago asked if data gets updated in the OG table if you update the view. I figured yea. So after interview I tested it. Created simple View from a some simple table. Wrote a simple update statement to update a column's value where PKId= whatever, then did selects against both the View and the OG table and column is updated in both result sets. Thus Yes you will update original table content from updating the view.