BUG: Inefficient Access Plan When Using Index for ORDER BY

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

       NT:  852 (4.2)

SYMPTOMS

When the optimizer decides to use an index to satisfy an ORDER BY condition, it may use an access plan that results in large numbers of page reads. This can result in slow query performance.

WORKAROUND

Expanding the number of columns in the ORDER BY clause will cause the optimizer to use a worktable for sorting the results instead of attempting to use the index. This will prevent one page I/O for each row from being performed.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2b for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

In some cases, the optimizer may decide to traverse an index in order to satisfy an ORDER BY clause if an index exists that fully covers the columns listed in the ORDER BY. If the WHERE clause of such a query contains references to columns that are not the first key of the index being used to satisfy the ORDER BY, SQL Server will scan all leaf rows of the index, performing a page read for each row in the table.

An example of such a query would be:

   create table testtab( col1 int, col2 int )
   go
   create unique index idx1 on testtab( col1 )
   go
   /* populate table */
   go
   select * from testtab
   where col1 > <value> and col2 = <value2>
   order by col1