Is a SqlServer View always up to date?

Question

Will a sqlserver view always be up to date or can there be a delay on it?

Answer 1

They are always up to date. A view is not a persisted object. When you query a view, you are actually querying the underlying tables etc. that make up the view. Therefore, your views will be as up to date as your tables.

Answer 2

a non-indexed view intrinsically can not store out of date data, it is ethereal and any query against it is really hitting the underlying table.

An indexed view does store it's own copy, but in the query plans you will see any insertion / update / deletion also includes the relevant change to the view so the data remains consistent.

Answer 3

That depends on the transaction isolation level the view runs at. By default, views run at READ COMMITTED. The view will return only committed data. As long as your view consists of only one SQL statement, and doesn't call user defined functions or extended procedures, it will be consistent.

But views can also run at a more risky isolation level. For example, this view specifies nolock so it runs at READ UNCOMMITTED:

create view dbo.MyView
as select * from dbo.MyTable with (nolock)

This view can return values that are part of a transaction that will be rolled back (dirty read.) This transaction isolation level trades consistency for performance.

Answer 4

A typical view (non-indexed / non-materialized) is just an alias for a SELECT. It can't be out of date any more than a SELECT can be.