Is Index Hinting available in NAV 2016? I don't find anything in the MSDN documentation on it. If not, is there a recommended alternate solution to controlling index selection in queries?
I have a situation where certain filter values are resulting in non-optimal query plans. The particular table (Item) has about 35,000 records. The filter is applied to a field (Item Category Code) that has a secondary index. If value "A" is applied (which returns about 50 records) the plan uses the expected index. However if value "B" is used (which returns about 5000 records) the plan does a clustered index scan. This is clearly an issue with the "B" filter not being selective enough.
I have tested the query in SQL using an index hint. It will use the expected index regardless of the filter value.
Any ideas or suggestions?
There are no bugs - only undocumented features.