FIX: ORDER BY DESC Queries May Cause High Logical Reads

• Microsoft SQL Server, versions 6.0 and 6.5

         15670 (6.50)

SYMPTOMS

A SELECT query containing an ORDER BY DESC clause can sometimes cause a high number of logical reads in SQL Server 6.0.

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

WORKAROUND

Restructure the query to use an intermediate temporary table such as the following:

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

   SELECT *
   FROM #tmp_inv
   ORDER BY inv_no DESC

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.