Hints in Views

Can we use hints with in the view definition ?
Thanks in advance.
RajYes, just be sure that you really need the hint, as specifying the wrong
hint can impact performance negatively.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Vish" <mocherla_v@.hotmail.com> wrote in message
news:ujpIbVeRDHA.3132@.tk2msftngp13.phx.gbl...
> Can we use hints with in the view definition ?
> Thanks in advance.
> Raj
>|||Raj,
I hope the below article helps...it is right from BOL.
View Hints
View hints can be used only for indexed views. (An indexed
view is a view with a unique clustered index created on
it.) If a query contains references to columns that are
present both in an indexed view and base tables, and
Microsoft SQL ServerT query optimizer determines that
using the indexed view provides the best method for
executing the query, then the optimizer utilizes the index
on the view. This function is supported only on the
Enterprise and Developer Editions of the Microsoft SQL
Server 2000.
However, in order for the optimizer to consider indexed
views, the following SET options must be set to ON:
ANSI_NULLS, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
ANSI_PADDING, ARITHABORT, QUOTED_IDENTIFIERS
In addition, the NUMERIC_ROUNDABORT option must be set to
OFF.
To force the optimizer to use an index for an indexed
view, specify the NOEXPAND option. This hint may be used
only if the view is also named in the query. SQL Server
2000 does not provide a hint to force a particular indexed
view to be used in a query that does not name the view
directly in the FROM clause; however, the query optimizer
considers the use of indexed views even if they are not
referenced directly in the query.
View hints are allowed only in SELECT statements; they
cannot be used in views that are the table source in
INSERT, UPDATE, and DELETE statements.
Syntax
< view_hint > ::={ NOEXPAND [ , INDEX ( index_val [ ,...n ] ) ] }
Arguments
NOEXPAND
Specifies that the indexed view is not expanded when the
query optimizer processes the query. The query optimizer
treats the view like a table with clustered index.
INDEX ( index_val [ ,...n ] )
Specifies the name or ID of the indexes to be used by SQL
Server when it processes the statement. Only one index
hint per view can be specified.
INDEX(0) forces a clustered index scan and INDEX(1) forces
a clustered index scan or seek.
If multiple indexes are used in the single hint list, the
duplicates are ignored and the rest of the listed indexes
are used to retrieve the rows of the indexed view. The
ordering of the indexes in the index hint is significant.
A multiple index hint also enforces index ANDing and SQL
Server applies as many conditions as possible on each
index accessed. If the collection of hinted indexes does
not contain all columns referenced in the query, a fetch
is performed after retrieving all the indexed columns.
DeeJay
>--Original Message--
>Can we use hints with in the view definition ?
>Thanks in advance.
>Raj
>
>.
>

hii

Hiiiiii
I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?(reneeshprabha@.gmail.com) writes:

Quote:

Originally Posted by

I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?

That would be:

SELECT o.name
FROM sysobjects o
WHERE NOT EXISTS (SELECT *
FROM sysdepends d ON o.id = d.depid)
AND o.type = 'V'

However, be very very careful. If you recreate a view, all dependency
information is lost, and thus that view will appear as unused in this
query.

It may be better to script the stored procedures to a text file, and
then search the files for occurrences of the view name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Hiding Report Parameters

I have a report with 3 parameters (Run Date, Branch, and Account Manager #).
We are using this report to show 3 different views (Corporate, Branch, and
Account Manager) and want to hide/disable the parameters that are not
required.
If the user wants to view the Corporate View, only the Run Date parameter is
to be displayed. The Branch and Account Manager # are defaulted to All using
the URL.
If the user wants to view the Branch View, only the Run Date and Branch
parameters are to be displayed. The Account Manager # is defaulted to All
using the URL.
Is it possible to hide these parameters in SQL Reporting Services 2000 using
the URL passed to the report?
If not, are there other ways?Yes. Push the report to a directory then create linked reports in 3
separate folders (corp, branch, acctMgr). You can specify whether or not
the parameter is hidden when you set up the linked report. You can then
grant the appropriate privileges on each of the three folders (don't grant
privileges to the three roles below on the primary report).
HTH
-Tim
"rwhitelaw" <rwhitelaw@.discussions.microsoft.com> wrote in message
news:FA85FF28-8F64-4FAC-80E1-8644629997CA@.microsoft.com...
>I have a report with 3 parameters (Run Date, Branch, and Account Manager
>#).
> We are using this report to show 3 different views (Corporate, Branch, and
> Account Manager) and want to hide/disable the parameters that are not
> required.
> If the user wants to view the Corporate View, only the Run Date parameter
> is
> to be displayed. The Branch and Account Manager # are defaulted to All
> using
> the URL.
> If the user wants to view the Branch View, only the Run Date and Branch
> parameters are to be displayed. The Account Manager # is defaulted to All
> using the URL.
> Is it possible to hide these parameters in SQL Reporting Services 2000
> using
> the URL passed to the report?
> If not, are there other ways?