Database View Does Not Reflect The Data in The Underying Table

Database VIEW does not reflect the data in the underying TABLE

Yes, sort of.

Possible Causes:

1. The View needs to be refreshed or recompiled. Happens when source column definitions change and the View (or something it depends on) is using "*", can be nasty. Call sp_RefreshView. Can also happen because of views or functions (data sources) that it calls as well.

2. The View is looking at something different from what they/you think. They are looking at the wrong table or view.

3. The View is transforming the data in an unexpected way. It works right, just not like they expected.

4. The View is returning a different subset of the data than expected. Again, it works right, just not like they think.

5. They are looking at the wrong database/server or with a Logon/user identity that causes the View to alter what it shows. Particularly nefarious because unlike Management Studio, most client programs do not tell you what database/server they are pointed at.

Why view is not updated if my table definition is updated?

When you create a view it stores the view definition as metadata in the system tables. Even if you use SELECT * FROM it will store the exact column names e.g. SELECT a, b FROM

If you then update the base table the metadata definition for the view still remains the same so any new columns won't be picked up.

You need to either drop and recreate the view or run sp_refreshview to update the definition

Do views immediately reflect data changes in their underlying tables?

If you've previously loaded the same Object entity in the same DbContext, EF will return the cached instance with the stale values, and ignore the values returned from SQL.

The simplest solution is to reload the entity before returning it:

var result = db.Objects.Single(c => c.ObjectId == objectId);
db.Entry(result).Reload();
return result;

Wrong column values SQL Server Views after table schema update

After updating schema, you should refresh the view metadata for any and all views dependent on the schema:

EXEC sp_refreshview @viewName

Where the @viewName variable holds the name of the view. You can use this stored procedure in a script that can grab the views dependent on the table(s) in order to refresh them all dynamically, or you can just spin through all views (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS) and call the stored procedure on every view.

This has the added benefit of finding any views that are rendered invalid by schema changes and flagging them, as they'll error out when the SP is called.

Mysql tables does not reflect new changes

Using CREATE TABLE with another table as the data source provides the target table with a one time snapshot of the data in the source table. Hence, making subsequent inserts into enrolment will not reflect in enrolment_status. The closest thing to what you have in mind might be to create a view on top of the enrolment table:

CREATE VIEW enrolment_view AS
SELECT code, COUNT(id)
FROM enrolment
GROUP BY code;

The view enrolment_view would reflect changes to the underlying table enrolment, including newly inserted records. But, it would not be possible to insert records directly into this view.

Are Views automatically updated

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://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15

Difference between View and table in sql

A table contains data, a view is just a SELECT statement which has been saved in the database (more or less, depending on your database).

The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.

Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

Do Changes Made on Table Created by Joining a View and an Another Table Affect Original Table View Based On?

In principle, yes, if the views are constructed in such a way that the underlying tables are updateable through the view. For example, let's say I have a table named TABLE_1 and create a view VIEW_1 as follows:

CREATE OR REPLACE VIEW VIEW_1 AS
  SELECT *
    FROM TABLE_1

If I issue the update statement

UPDATE VIEW_1
  SET FIELD_N = 'XYZ'
  WHERE KEY_1 = 123

Oracle is bright enough to pass the UPDATE through to the underlying table, and TABLE_1 will be updated.

A view of any complexity, however, will most likely contain operations that make the view non-updateable. So let's say I have the following VIEW_2:

CREATE OR REPLACE VIEW VIEW_2 AS
  SELECT KEY_1,
         FIELD_N,
         SUM(SOME_OTHER_FIELD) AS OTHER_SUM,
         MIN(YADDA_YADDA) AS MIN_YADDA
    FROM TABLE_1
    GROUP BY KEY_1,
             FIELD_N

an UPDATE of this view will fail with an ORA-01732: data manipulation operation not legal on this view error. So whether you can update through a view or not very much depends on what operations the view is performing.

db<>fiddle here

Will a SQL view filtered by large datetime always provide current data?

If I create a view today for a table that continuasly keeps getting data, [...] will it keep showing the current data to the users that can Access it?

Yes. A view does not actually stores data, it is just a sql query. Whenever you access the view, that sql query is executed under the hood. So basically a view always reflect the data that is stored in its underlying, physical tables.

Here is the small demo that demonstrates this behavior:

create table mytable (id int, val varchar(5));
insert into mytable(id, val) values(1, 'foo')

create view myview as select id, val from mytable;
select * from myview;

id | val
-: | :--
 1 | foo

insert into mytable values(2, 'bar');
select * from myview;

id | val
-: | :--
 1 | foo
 2 | bar

---

Related Topics