How are views used each time it is requested.

2 Feb 2006 7:55 PM

CD

Taking the view below, is a view reran each time it is requested or is there
some sort of caching that would hold the dataset ready for the next request
of the view?

CREATE VIEW dbo.vwTodaysNeurologyAppts
AS
SELECT     TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityType
FROM         dbo.Appointment
WHERE     (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
ORDER BY ApptDate

2 Feb 2006 8:12 PM

Tibor Karaszi

No caching of the dataset, as that would be incredibly complex considering that the underlying data
might have been modified in between. The data that has been accessed by the query you ran against
the view can of course be cached (just as if you were querying those tables directly).

You can create an index on a view (under certain circumstances) which essentially materializes the
view. Any modification if the underlying data need to be propagated to the materialized/indexed
view.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"CD" <mcd***@hotmail.REMOVETHIS.com> wrote in message
news:OfR%23iKDKGHA.2992@tk2msftngp13.phx.gbl...
> Taking the view below, is a view reran each time it is requested or is there some sort of caching
> that would hold the dataset ready for the next request of the view?
>
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT     TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityType
> FROM         dbo.Appointment
> WHERE     (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate, GETDATE()) = 0) AND
> (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>
>

2 Feb 2006 8:13 PM

Mark Williams

Each SELECT statement performed against the view will result in a SELECT
statement against the base tables. Data is cached in memory to make
subsequent SELECTS against the view faster, but the actual view data is
stored only in the base tables unless you create a clustered index on the
view. Indexes with views, also known as materialized views, have a seperate
data store for the columns in the view within the clustered index.

If you wanted to index your view, you would have to remove the TOP clause,
which would also necessitate removing the ORDER BY clause.

--

Show quote

"CD" wrote:

> Taking the view below, is a view reran each time it is requested or is there
> some sort of caching that would hold the dataset ready for the next request
> of the view?
>
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT     TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityType
> FROM         dbo.Appointment
> WHERE     (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
> GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>
>
>

2 Feb 2006 8:13 PM

Dean

no, the results are not cached between executions - but the source data
pages are, and depending on the level of activity on the server, there is a
good chance that they will still be in memory on successive executions.
however, you can persist the view by creating a clustered index on it - see
"indexed views" in BOL.

btw, don't rely on "SELECT TOP 100 PERCENT" and "ORDER BY" inside a view.
ORDER BY should be specified in the final SELECT. also, if there is an index
on ApptDate column, don't use DATEDIFF on it because the index won't be
used.

dean

Show quote

"CD" <mcd***@hotmail.REMOVETHIS.com> wrote in message
news:OfR%23iKDKGHA.2992@tk2msftngp13.phx.gbl...
> Taking the view below, is a view reran each time it is requested or is
> there some sort of caching that would hold the dataset ready for the next
> request of the view?
>
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT     TOP 100 PERCENT PatientName, ResourceCode, ApptDate,
> ActivityType
> FROM         dbo.Appointment
> WHERE     (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
> GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>
>