Re: Need help with hint syntax in SQL Server 2000

Mark D Powell (Mark.Powell@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, esquel@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

.

Relevant Pages

  • Re: Update Identity Column
    ... (not all of it SQL Server, mind you), so believe me, I know my way around. ... (x INT NOT NULL PRIMARY KEY CLUSTERED, ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)
  • Updating Combo box if Table is in SQL Backend
    ... I want to update the Vendor tbl with a new entry if it is not already present ... code below) but it does not update the tbl if I move it onto the SQL back end. ... Dim rstVendor As Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL 2000 and UTF-16 encoding
    ... >> SELECT * FROM tbl WHERE lastname Collate ... > So I guess you really problem is why you have UTF-8 encodings in the ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Update Table via Combo Box
    ... I cannot update the Combo Box if the tbl is created in SQL. ... Response As Integer) ... Dim rstCompetitor As Recordset ...
    (microsoft.public.access.gettingstarted)
  • Updating Combo box if Table is in SQL Backend
    ... I cannot update the Combo Box if the tbl is created in SQL. ... Response As Integer) ... Dim rstCompetitor As Recordset ...
    (microsoft.public.access.adp.sqlserver)