There are cases where you create indexes to support query elements that rely on order, but the query optimizer seems to not realize this, and ends up applying a sort. This session demonstrates a number of such situations and provides workarounds that enable the optimizer to rely on index order and this way avoid unnecessary sorting.
Presented by Itzik Ben-Gan at SQLBits 2019