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?
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'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.