12

I'm trying to execute the following command to force an update to use a non-clustered index:

 UPDATE Flights 
 SET Airtime=5555678
 WHERE Distance=10000
 OPTION (TABLE HINT(Flights, INDEX (DistanceIndex)))

... and it's erroring:

Msg 8724, Level 16, State 1, Line 75
Cannot execute query. Table-valued or OPENROWSET function 'Flights' cannot be specified in the TABLE HINT clause.

I can't find any clear advice on how the syntax should be properly formed inside the TABLE HINT statement.

Please can anyone advise where I'm going wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jon295087
  • 731
  • 1
  • 8
  • 28

2 Answers2

13

Try following:

UPDATE F
SET F.Airtime=5555678
FROM Flights F WITH (INDEX (DistanceIndex))
WHERE F.Distance=10000

or

UPDATE Flights
SET Airtime=5555678
FROM Flights -- this line is added to your initial query
WHERE Distance=10000
OPTION (TABLE HINT(Flights, INDEX (DistanceIndex)))
i-one
  • 5,050
  • 1
  • 28
  • 40
  • many thanks for your comments. Both the above methods worked for me, with identical impact on the execution plans. Kind regards, Jon – Jon295087 Jul 27 '14 at 07:40
4

@i-one's answer is correct and the recommended way.

You can also use

UPDATE F
SET    Airtime = 5555678
FROM   Flights F
WHERE  Distance = 10000 
OPTION (TABLE HINT(F, INDEX (DistanceIndex)))

But the documentation states

We recommend using the INDEX, FORCESCAN or FORCESEEK table hint as a query hint only in the context of a plan guide.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • @MartinSmith- many thanks for your comments. Appreciate that this approach rails against best practice - I was just interested to note the performance difference in the operation if I could force the use of a [less efficient] secondary index. I read further into the use of FORCESCAN and FORCESEEK, as i've not looked at these approaches. Kind regards, Jon – Jon295087 Jul 27 '14 at 07:44