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