Re: Need help with hint syntax in SQL Server 2000

On Dec 6, 11:00 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Mark D Powell (Mark.Pow...@xxxxxxx) writes:

Blast.  Based on < table_hint > ::=
    { INDEX ( index_val [ ,...n ] )
I was doing:  with index (index_name)

There are just too many ways to specify index hints!

What you missed was the extra layer of parenthesis, found higher up
in the topic. This is what you should try:

   WITH (INDEX (myindex))

I see that the syntax I suggested is not in Books Online 2000, but it
appears in Books Online 2008, although it seem to suggest that I should
say:

   WITH (INDEX = (myindex))

Nevertheless, I am quite sure that my original proposal works.

I know a FROM (table) hint on a query against a view is propogated to
the tables in the view but will an index hint propogate or will I have
to place the hint into the view itself?  

I think you will have to stick the hint in the view itself. Which is
something I would try to avoid. But try using the query as such with
the hint and see if it helps.

But run UPDATE STATISTICS WITH FULLSCAN on the tables first.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for the update. I did not get to work on this yesterday but
I intend to try to test today.

-- Mark D Powell --
.

Relevant Pages

  • Re: Need help with hint syntax in SQL Server 2000
    ... I see that the syntax I suggested is not in Books Online 2000, ... the tables in the view but will an index hint propogate or will I have ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • Re: Forcing index usage...
    ... I'm in a situation where oracle spatial doesn't use a domain index even if I use the index hint. ... But as soon as I add more predicates or nest the query into another one, ... Could you give us an example of the SQL ... Complete with execution plans. ...
    (comp.databases.oracle.server)
  • Re: Need help with hint syntax in SQL Server 2000
    ... the tables in the view but will an index hint propogate or will I have ... SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx ... The query was a simple query against a view: ... When I coded the index hint the SQL Server plan showed it being used ...
    (comp.databases.ms-sqlserver)
  • Re: PARTITION HASH INLIST -- QUERY
    ... Predicate Information: ... I have been able to tune the SQL by using use_concat hint. ... Also the PARTITION HASH INLIST will change to PARTITION HASH ITERATOR. ...
    (comp.databases.oracle.server)
  • Re: Error : Cannot specify an index or locking hint for a remote data source
    ... specifies the hint? ... "John Williams" wrote in message ... > We are migrating from sql 7 to sql 2000 and for the time ... > Cannot specify an index or locking hint for a remote data ...
    (microsoft.public.sqlserver.server)