Are Views automatically updated

Question

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?

No, the quickest way is to google search :) – TheTechGuy Jan 03 '18 at 07:08 — TheTechGuy, Jan 03 '18 at 07:08

Curtis · Answer 1 · 2014-08-01T11:42:03.327

105

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.

edited Aug 01 '14 at 11:42

answered Oct 13 '11 at 14:09

Curtis

101,612
66
270
352

+1 : Or a macro that is expanded inline, forming part of the enclosing code, rather than a pre-calculated result set it self. – MatBailie Oct 13 '11 at 14:15
1

If the data in a view is not stored anywhere, when you add a row to a view `INSERT INTO dbo.MyView (myCol) VALUES ('test')` - where is it stored? – BornToCode Apr 27 '15 at 20:39
4

You can't insert records into a view – Curtis Apr 27 '15 at 20:40
Sorry, I think I just found the answer - the data will be inserted to the underlying table (in case all the insert columns are from a single underlying table). – BornToCode Apr 27 '15 at 20:46
1

Just to clarify, you CAN insert records into a view if the view follows a set of requirements detailed [on MSDN](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql#updatable-views): all the columns must come from one base table, the columns can't be derived, the columns aren't affect by statements like `GROUP BY`, `TOP` with `WITH CHECK OPTION` can't be used, etc. See the link for more details. – Seafish Jan 09 '18 at 19:54
"A view can be thought of as either a virtual table or a stored query.": Well, it's better to think of it as a stored _subquery._ A "whole" query has a query plan. A view does not. A view doesn't even exist in a query plan; It's decomposed like the rest of the query. – Tom Blodget May 29 '19 at 17:34

score 29 · Answer 2 · answered Oct 13 '11 at 14:14

29

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.

answered Oct 13 '11 at 14:14

OTTA

1,071
7
8

score 11 · Accepted Answer · answered Oct 20 '21 at 15:22

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

*2 https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15

Changed my selected answer to this after asking the question 10 years ago :) I verified Indexed View are stored (materialized) and does not have to fetch fresh data, it is already cached, refreshed before it is run, ideally suited for Warehouse environment - [video tutorial](https://www.youtube.com/watch?v=mvW_TCQnrKw) — TheTechGuy, Oct 22 '21 at 01:13

score 7 · Answer 4 · answered Oct 13 '11 at 14:07

7

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.

answered Oct 13 '11 at 14:07

score 6 · Answer 5 · answered Jan 03 '18 at 07:06

6

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

answered Jan 03 '18 at 07:06

Yogen Darji

3,230
16
31

score 4 · Answer 6 · answered Apr 04 '16 at 19:03

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.

score -2 · Answer 7 · answered Aug 27 '19 at 18:07

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.

This does not really answers the question. View can be update the underlying table through view only if it contains only one table, has primary key etc [more here](https://www.w3resource.com/sql/update-views/sql-update-views.php) — TheTechGuy, Aug 30 '19 at 16:55