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
>
>

how 2 know the most requested tables

Is there a way to know the tables the mostly requested in a database
How please?

Because we want to copy indexes from DB2 to SQl tables, but since there re so many tables in the DB we want to start with the most requested tables by the applications

Thanks

Do you want to know from the DB2 database or the SQL Server database?

If you are talking about SQL Server 2005 you can check the DMFs sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
I want to know the most used tables in SQL 2005 database, but now I have no indexes defined yet. That s why I need to know the most used tables so that we can start creating indexes for these tables first.

The thing is we have to fix the problemes as soon possible, that s why we need to start with the most important tables , that s the mostly used tables so that we create their indexes first

Thanks a lot
|||

If you are on SP 2 (install it if you aren't), you really want to look at the Performance Dashboard Reports. That will help immensily in what you are trying to do. They are meant for troubleshooting performance problems. Looking at just the most used tables may not get you there as quickly as using these reports. You can drill through on missing indexes and look at the gains, impact you get from different indexes. The index recommedations is what you are looking for anyway based on what you just posted. In addition, the problems you are having may be more than just indexes and these reports will help with that. Check the following article and link to the reports:

http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx

-Sue