Microsoft KB Archive/131906

{|
 * width="100%"|

BUG: Optimizer May Not Choose Correct Index

 * }

Q131906

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

BUG# NT: 7859 (4.21a)

SYMPTOMS
The optimizer may not choose to use a valid non-clustered index if the underlying varchar column has many repeat values of " " (space.)

WORKAROUND
Choose another value for these rows or force the index selection.

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
In the replication, the column had approximately 24,000 rows, all but five of which were a single space. When you select * from the table based on one of the five non-space values, it chose to do a table scan. It did use the index in a covered query, but not in uncovered. The problem has not been replicated for any values other then a combination of spaces, including NULL, but that possibility exists. Somewhere between 23,000 and 24,000 rows is the breaking point.

Additional query words: 5 Windows NT

Keywords : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2