BUG: Optimizer May Not Choose Correct Index

• Microsoft SQL Server, version 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.