Re: Need help with hint syntax in SQL Server 2000
- From: Mark D Powell <Mark.Powell@xxxxxxx>
- Date: Sat, 6 Dec 2008 07:39:01 -0800 (PST)
On Dec 6, 8:10 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Mark D Powell (Mark.Pow...@xxxxxxx) writes:
I have only used hints a few times and it has been a while. Every
time I try to play with an SQL statement to force use of index over a
non-indexed path so I can compare the two approaches it take me
forever because I cannot remember how to code the hints. I just
cannot figure it out form the books on-line syntax diagrams.
Can someone point to to an article which has numerous examples of how
to code the hints into the SQL?
It will probably eventually clink since it has in the past, but I
would rather not spend a couple hours on whatever stupid mistake I am
making.
FROM tbl alias WITH (INDEX = yourindex)
or
FROM tbl WITH (INDEX = yourindex)
if you don't use aliases.
The WITH keyword is not mandatory in SQL 2000, but it is SQL 2005, so start
using it.
If you want to force the clustered index, I think it's better to say:
FROM tbl WITH (INDEX = 1)
as a name easily could to wrong. But for non-clustered indexes, names is
better over index ids.
There are some older syntax that you have used in the past. Forget about
those.
--
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
Blast. Based on < table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
I was doing: with index (index_name)
I will have to the correct syntax on Monday and see if I can get the
optimizer to try the index. It is a simple query with one condition on
a view that is a union of two joins. The view is written using the
new with ANSI 92 join syntax. We scan a small 138 row table and then
scan a 5.9M row tables. I am trying to index the column the join is
done on and a second column that is used to filter the large table for
a time test. I haven't worked out how selective the index will be yet
as I have to present proof that an index would not fix the problem. I
have suggested purging as an alternate if I cannot find an index that
will help.
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 have already created a copy
of the real view to use in my testing but I ran into the syntax error
again so I haven't had the opportunity to see if the hint propogates
or now.
Thank you for the reply.
-- Mark D Powell --
.
- Follow-Ups:
- Re: Need help with hint syntax in SQL Server 2000
- From: Erland Sommarskog
- Re: Need help with hint syntax in SQL Server 2000
- References:
- Need help with hint syntax in SQL Server 2000
- From: Mark D Powell
- Re: Need help with hint syntax in SQL Server 2000
- From: Erland Sommarskog
- Need help with hint syntax in SQL Server 2000
- Prev by Date: Re: Need help with hint syntax in SQL Server 2000
- Next by Date: Re: Need help with hint syntax in SQL Server 2000
- Previous by thread: Re: Need help with hint syntax in SQL Server 2000
- Next by thread: Re: Need help with hint syntax in SQL Server 2000
- Index(es):
Relevant Pages
|
