FIX: ORDER BY DESC Queries May Cause High Logical Reads

BUG# NT: 12057 (6.00)
         15670 (6.50)
		

CAUSE

This can occur if the optimizer chooses to process the query by using an index on the ordered column and the WHERE clause of the query includes a range search such as the following:

   SELECT *
   FROM inv_table
   WHERE inv_no BETWEEN 100 AND 500
   ORDER BY inv_no DESC
				

You can determine whether the optimizer has chosen to use an index by using the SET SHOWPLAN ON command and viewing the showplan output as documented in Chapter 23 of the SQL Server "Administrator's Companion." You can view the number of logical reads for the query by using the SET STATISTICS IO ON command.

   SELECT * INTO #tmp_inv
   FROM inv_table
   WHERE inv_no BETWEEN 100 AND 500

   SELECT *
   FROM #tmp_inv
   ORDER BY inv_no DESC