You are currently offline, waiting for your internet to reconnect

FIX: A SELECT query unexpectedly returns an incorrect number of rows in SQL Server 2005 Mobile Edition and in SQL Server 2005 Compact Edition

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
When you run a SELECT query in Microsoft SQL Server 2005 Mobile Edition and in Microsoft SQL Server 2005 Compact Edition, the query unexpectedly returns an incorrect number of rows. This problem occurs if the following conditions are true:
  • The SELECT query contains an inner join.
  • One of the two tables that are joined has an index on the joined column. The other table has no index on any of its columns.
For example, you run the following query in SQL Server 2005 Mobile Edition:
select * from T1, T2 where T1.Col = T2.Col1 order by T1.Col
Note In this example, table T1 has an index on column Col. Table T2 does not have an index on column Col1.
CAUSE
This problem occurs because the query optimizer does not discard the previous plan completely. The query optimizer considers multiple query plans before the query optimizer decides on the best plan to execute a query. In some cases, the query optimizer can find a plan that uses an index to evaluate the condition. However, the query optimizer may later find a better plan. In this case, the query optimizer discards the previous plan to use the better plan.
RESOLUTION
To resolve this problem, use one of the following methods:
  • Create an index on the joined column on the second table.
  • Delete the index of the joined column on the first table.
Note In the example in the "Symptoms" section, the joined column on the second table is T2.Col1. The joined column on the first table is T1.Col .
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION

Steps to reproduce the problem

  1. Run the following statements against a database in SQL Server 2005 Mobile Edition:
    CREATE TABLE T1 (COL INT);GO;CREATE INDEX T1_IDX ON T1 (COL);GO;INSERT INTO T1 VALUES (1);INSERT INTO T1 VALUES (2);INSERT INTO T1 VALUES (3);GO;CREATE TABLE T2 (COL1 INT, COL2 INT);GO;INSERT INTO T2 VALUES (1,1);INSERT INTO T2 VALUES (2,2);INSERT INTO T2 VALUES (3,3);GO;
  2. Run the following statement:
    select * from T1, T2 where T1.Col = T2.Col1 order by T1.Col
    You experience the problem that is described in the "Symptoms" section.
Properties

Article ID: 933697 - Last Review: 03/13/2007 19:39:22 - Revision: 1.2

Microsoft SQL Server 2005 Mobile Edition, Microsoft SQL Server 2005 Compact Edition

  • kbtshoot kbexpertiseadvanced kbbug KB933697
Feedback