BUG: Combination of Top, an Inequality, and a Join Result in Incorrect Choice of a Table Spool Plan (266617)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q266617
BUG #: 58107 (SQLBUG_70)

SYMPTOMS

When a query has the TOP keyword and an inequality (such as <, <=, >, >=, LIKE, or BETWEEN) combined with a join, the optimizer may incorrectly choose a TABLE SPOOL plan which results in decreased performance.

CAUSE

SQL Server may incorrectly estimate the cost for certain plans that involve the TOP command, leading to the choice of a less than optimal plan.

WORKAROUND

To work around this problem:
  • Don't use the TOP keyword in combination with a join and an inequality.
  • Use a join or index hint.
  • Use Microsoft SQL Server 2000, which has changes in the way that SQL Server handles buffer calculations.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.
Modification Type:MajorLast Reviewed:9/4/2002
Keywords:kbbug kbWorkFlowDesigner KB266617
©2002 Microsoft Corporation. All rights reserved.