How are views used each time it is requested.

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 ApptDateNo caching of the dataset, as that would be incredibly complex considering t
hat 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 essent
ially materializes the
view. Any modification if the underlying data need to be propagated to the m
aterialized/indexed
view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CD" <mcdye1@.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 the
re 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, ActivityTy
pe
> FROM dbo.Appointment
> WHERE (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate, GE
TDATE()) = 0) AND
> (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>|||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
"CD" <mcdye1@.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
>|||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.
"CD" wrote:

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