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

Hints in T-SQL (SQL Server 2000)

Hello,
I want the help on the SQL Server HINTS. In Oracel we use the hints in the following manner:
SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
Similarly, i found the help on SQL Server HINTS. They are used with the Keyword 'OPTION' at the end of SELECT statement.
Here i want to know more about the hints. In specific, i would like to know about the 'OPTION (FORCE ORDER)' hint in sql server.
If any one if you have any idea regarding this then do let me.
Thanking all of u in advance.
Aparna,
Still migrating things? SQL Server uses a cost based
optimizer and it's generally better to let SQL Server figure
out the most efficient plans. In general, you don't see
hints being used in SQL Server to the same degree they are
used in Oracle. For the most part, you'd want to have a good
specific reason for using them in SQL Server. I've seen
cases where hints being used actually hurt performance more
than helped. Have you come up with something specific on the
execution of a query that you'd want to use this?
-Sue
On Mon, 12 Apr 2004 21:36:04 -0700, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>I want the help on the SQL Server HINTS. In Oracel we use the hints in the following manner:
>SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
>Similarly, i found the help on SQL Server HINTS. They are used with the Keyword 'OPTION' at the end of SELECT statement.
>Here i want to know more about the hints. In specific, i would like to know about the 'OPTION (FORCE ORDER)' hint in sql server.
>If any one if you have any idea regarding this then do let me.
>Thanking all of u in advance.

Hints in T-SQL (SQL Server 2000)

Hello,
I want the help on the SQL Server HINTS. In Oracel we use the hints in the f
ollowing manner:
SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
Similarly, i found the help on SQL Server HINTS. They are used with the Keyw
ord 'OPTION' at the end of SELECT statement.
Here i want to know more about the hints. In specific, i would like to know
about the 'OPTION (FORCE ORDER)' hint in sql server.
If any one if you have any idea regarding this then do let me.
Thanking all of u in advance.Aparna,
Still migrating things? SQL Server uses a cost based
optimizer and it's generally better to let SQL Server figure
out the most efficient plans. In general, you don't see
hints being used in SQL Server to the same degree they are
used in Oracle. For the most part, you'd want to have a good
specific reason for using them in SQL Server. I've seen
cases where hints being used actually hurt performance more
than helped. Have you come up with something specific on the
execution of a query that you'd want to use this?
-Sue
On Mon, 12 Apr 2004 21:36:04 -0700, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>I want the help on the SQL Server HINTS. In Oracel we use the hints in the
following manner:
>SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
>Similarly, i found the help on SQL Server HINTS. They are used with the Key
word 'OPTION' at the end of SELECT statement.
>Here i want to know more about the hints. In specific, i would like to know
about the 'OPTION (FORCE ORDER)' hint in sql server.
>If any one if you have any idea regarding this then do let me.
>Thanking all of u in advance.sql

Hints ?

Is there anything equivalent available in SQL Server for Oracle HINTS ?
eg : Oracle query
select /* + INdex(sno index1) */ sno from test_table
Thanks,
SamYes they are also called "HINTS". Please look at SQLServer Help text (Query Analyzer -> Help) and search on "HINTS" which has details that you need.

Vinnie|||And you're better off not using them unless the box is on fire...

MOO

Hints

I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.

>From BOL, it seems that one can specify them with "WITH (...)" clauses
in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?

Cheers!<newtophp2000@.yahoo.com> wrote in message
news:1104122591.142300.19090@.f14g2000cwb.googlegro ups.com...
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> >From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Generally, "don't".

Usually SQL server will make better guesses than you can.

> Cheers!|||>> what is the general practice? Use hints or not? <<

1) "Trust in the Optimizer, Luke!" It is usually smarter than you are.
What happens when you give a bad hint?

2) Once you write a query with a hint, that hint stays there. Even if
the pathological situation that made you use a hint heals up. Nobody
will dare remove it later, since it looks important.

3) Every product that supports hints has a different syntax and
underlying model, so your hint code will not port, and the logic of
your hint might not port either.

For example, in Sybase SQL Anywhere, you can give a guess as to what
percentage of the time a predicate will be TRUE. Their optimizer uses
that guess instead of it own computation to build the query. It is not
forced to use a particlar index or method. like other products.|||--CELKO-- wrote:

> 2) Once you write a query with a hint, that hint stays there. Even if
> the pathological situation that made you use a hint heals up. Nobody
> will dare remove it later, since it looks important.

That's funny! And too true!

Zach|||(newtophp2000@.yahoo.com) writes:
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Be very conservative with adding hints. In an ideal you would never have to
use them, but today I add two hints to one query: one index hint, and one
OPTION clause to turn of parallelism.

My general rule is that I add a hint, if 1) there is an apparent performance
problem and 2) there is an obvious choice of how the query plan should go.
The one hint I am the least conservative is OPTION (MAXDOP 1), because
even if the query would execute faster with parallelism, it will not at
least monopolize all processors in the machine. (And often parallel plans
are more ineffecient than the non-parallel plans.) The hint I am most
conservative of using is the join hint - you dump in a word between
INNER and JOIN, since this use of this hint results in a warning.

Whether to use WITH or OPTION depends on what you want to force. They
serve different purposes, therefore you cannot say that one is better
than the other.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> That's funny! And too true! <<

Remember the quote from early UNIX days? "There is nothing more
permanent than a temporary patch!"