Index hint in delete statement?

According to what I see in BOL, the following should work:

delete

from dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

Yet when I syntax check this I get:

Msg 1069, Level 15, State 1, Line 2

Index hints are only allowed in a FROM clause.

(Please ignore the fact that index hints are unnecessary / a bad idea / etc.)

What you wrote is the equivalent of:

delete

dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

So really there's no FROM clause, and the error message returned is correct. If you're going to use a FROM clause, you need to specify what tables you're deleting from. i.e.:

delete dbo.tbl1

from dbo.tbl1 WITH (INDEX(idx_un01))

where tbl1_no = 1

In theory, you can join multiple tables in your FROM clause, which is why you need to indicate what table you want to delete.

|||

Hmmm. Interesting. What is even more interesting is that it accepts the WITH (ROWLOCK) hint without a quibble. I also don't see this syntax as being required in BOL. (It is only mentioned for joins / correlated subqueries.)

ref: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ed6b2105-0f35-408f-ba51-e36ade7ad5b2.htm

|||

The syntax is TSQL extension for UPDATE/DELETE statement. The ANSI SQL syntax for UPDATE and DELETE does not have a FROM clause at all. So there are two FROM clauses in the TSQL version of DELETE. I think it is a bug that we allow the locking hint and not the INDEX hint. IMO, both should be allowed without the FROM clause containing the table sources. You may want to file a bug at http://connect.microsoft.com. And both FROM clauses are optional also. See the DELETE statement topic in BOL where it is documented completely.

Generally speaking you should try to use the ANSI SQL syntax as far as possible and avoid using hints in most DML statements. The hint is just a hint and it can be overridden due to resource constraints for example. And by forcing certain indexes you are restricting the plan choices for the query optimizer also. Is there any specific reason why you want to use the hint in the DELETE statement? Are you having some wrong plan choices for the DELETE statement? If so, that might be a bug and it will be good if you can post a repro for that.