Sort Rules, order by & Index Hints in Dynamics 365 Finance & Operations
Question 32 — Sorting result sets efficiently and letting the database optimizer do the work.
Interview Question
order by preferred over index / index hint,
and what field types should you avoid sorting on?
Model Answer (Short)
Sorting can be expensive, so you should sort only when necessary. When you do
need ordering, use the order by keyword and let the database optimizer
choose the best execution plan — do not sort using the index or
index hint keywords, which force a specific index and can hurt performance. For best results, the
fields you sort on should be numeric or indexed; avoid sorting on long string types
such as memo fields, because they are slow to sort.
Sorting Best Practices
Core rules
- Sort only when necessary — sorting has a real cost.
- Use
order byto delegate control to the database optimizer. - Do not sort with the
indexorindex hintkeywords. - Prefer sort fields that are numeric or indexed.
- Avoid sorting on long string / memo fields.
Why order by beats index hints
order bylets the optimizer pick the most efficient plan for current data volumes and statistics.- An index hint forces one index, which may become suboptimal as data grows or distribution changes.
- Hard-coded hints reduce flexibility and can degrade performance after data changes.
Prerequisites (Rule 5)
- Visual Studio with the Dynamics 365 developer tools.
- A custom model / package for your objects.
- Indexes supporting your order-by fields where sorting is frequent.
Code Example — Sorting with order by
// GOOD: let the optimizer choose the plan via order by
SalesTable salesTable;
while select SalesId, CreatedDateTime from salesTable
order by salesTable.CreatedDateTime desc
{
// business logic
}Avoid: forcing an index / index hint
// AVOID: forcing a specific index for sorting
SalesTable salesTable;
while select salesTable
index hint SalesIdx // avoid — ties the query to one index
{
// ...
}
// PREFER: order by instead
while select salesTable
order by salesTable.SalesId // optimizer decides
{
// ...
}Prefer numeric / indexed sort fields
// GOOD: sort on an indexed numeric field
AbcTransaction trans;
while select trans
order by trans.TransRecId asc // numeric / indexed -> fast
{
// ...
}
// AVOID: order by trans.Remarks // long string / memo -> sloworder by vs. index / index hint
| Aspect | order by | index / index hint |
|---|---|---|
| Who chooses the plan | Database optimizer | Developer (forced) |
| Adapts to data growth | Yes | No |
| Risk over time | Low | Can become suboptimal |
| Recommended | Yes | Avoid for sorting |
Points the interviewer wants to hear
- Sort only when necessary — it's expensive.
- Use
order byand let the optimizer decide. - Avoid the
index/index hintkeywords for sorting. - Sort on numeric or indexed fields.
- Never sort on long strings / memo fields.
Likely Follow-up Questions
- Why can an index hint become suboptimal over time?
- Why is sorting on a memo field discouraged?
- How does
order byinteract with existing indexes? - When is it acceptable to skip sorting entirely?
Key Takeaway
Sort only when you must, and always use order by so the
database optimizer picks the best plan. Avoid forcing index / index hints for
sorting, and keep sort fields numeric or indexed — never long strings or memo fields.