If I recall correctly, Itzik Ben-Gan suggested part of the index on the date in DESC order in this article
Quote from this article:
Regardless of the solution you use, if you can afford to create optimal indexing, the best approach is to create an index on the partitioning column (e.g., custid, if you need top rows per customer), plus the ordering columns as the keys (orderdate DESC, orderid DESC), and include in the index definition all the other attributes you need to return from the query (e.g., filler). As I mentioned, I’ll use custid as the partitioning column to demonstrate queries with low density and shipperid for high density.
commented on Jul 12 2011 7:34PM