Incorrect query results for execution plan, unsure of exact cause of problem We had a query yesterday that was intended to page through results but on the first page of results the query only returned 4 records instead of the expect 25. Changing the query in any way resulted in 25 records which meant to me that it was an execution plan issue. I'm not familiar with a way to view execution plan in production and running the query in sql studio didn't result in same problem, probably due to minor differences.

I read suggestions that such a thing could be caused by a corrupted index. I ran a checkdb on the database and it found no errors. In the end I cleared the execution plans and all has been well.

If it wasn't a corruption issue of some kind and only a problem for the very specific execution plan then does that mean there was simply an error in the execution plan and we are hitting a bug in SQL Server? We are on SQL Server 2012 RTM with no updates so I looked through documentation on all fixes in cumulative updates and service packs but none of the issues appeared to related to our own.

Any other ideas or thoughts as to what could cause this?

(@P1 varchar(8000),@P2 bit,@P3 varchar(8000),@P4 bit,@P5 varchar(8000))

SELECT e.*
FROM (

    SELECT 

        TOP 25
        ROW_NUMBER() OVER (
            ORDER BY AddedDate DESC
        ) AS Row,

        ID
        , Prefix
        , FirstName
        , LastName
        , Company
        , Address
        , City
        , State
        , Zip
        , Country
        , WorkPhone
        , HomePhone
        , MobilePhone
        , Email
        , MailingLists
        , AddedDate
        , AwaitingOptin
        , OptInDate
        , Processed
        , ProcessedDate
        , Deleted
        , Source
        , GRRecID
        , DatabaseID
        , (SELECT COUNT(*) 
            FROM TableA
            LEFT OUTER JOIN TableB ON TableA.GRRecID = TableB.GRRecID
            WHERE TableA.AccountID = @P1 
                AND TableA.Email = TableC.Email
                AND TableA.Deleted = @P2 
                ) AS Matches
    FROM TableC
    WHERE AccountID = @P3  
        AND Processed = @P4  
        AND Deleted = 0
        AND Source = @P5  
) AS e
WHERE Row &gt;= 1 AND Row &lt;= 25
ORDER BY Row
I don't believe that result was incorrect at all, your query is ambiguous. Semantically your subquery enumerates all rows and then you pick the top 25 rows without specifying an order by. That means that SQL server is at liberty to return any rows it feels like and those rows do not have to be the rows enumerated 1 to 25. It can be just any random rows and in your case four of the rows was enumerated in the range 1 to 25. The rest was filtered out in the main query. 

You can fix the query by removing the top statement in the subquery.


  In your opinion based on the query what would be the best course of
  action?


I would test all the options you have and pick the one that performs the best. 

I will give you one more option to test that assumes ID is the primary key column in TableC and that AddedDate is not the clustered key.

Add an index on AddedDate including the columns you need to filter out rows before the enumeration happens.

create index IX_TableC_AddedDate on TableC(AddedDate) 
  include(AccountID, Processed, Deleted, Source)


Rewrite your sub query so that the only returns ID and row and then join back to TableC in the main query to fetch the rest of the columns.

SELECT E.Row,
       C.*, -- don't use * here, include the columns you actually need
       (
       SELECT COUNT(*) 
       FROM TableA as A
         LEFT OUTER JOIN TableB AS B 
            ON A.GRRecID = B.GRRecID
       WHERE A.AccountID = @P1 AND 
             A.Email = C.Email AND 
             A.Deleted = @P2 
       ) AS Matches
FROM (
     SELECT ROW_NUMBER() OVER (ORDER BY C.AddedDate DESC) AS Row,
            C.ID
     FROM TableC as C
     WHERE AccountID = @P3 AND 
           Processed = @P4 AND 
           Deleted = 0 AND 
           Source = @P5  
     ) AS E
  INNER JOIN TableC as C -- Join back to TableC
    ON E.ID = C.ID
WHERE E.Row &gt;= 1 AND E.Row &lt;= 25
ORDER BY E.Row


There might be an even more efficient index you can try but the datatypes for your parameters are somewhat confusing. Is AccountID really a varchar(8000)? If not you should change the data type to match that of the column and in that case a better index to use would be to lead of with AccountID. 

create index IX_TableC_AccountID_AddedDate on TableC(AccountID, AddedDate) 
  include(Processed, Deleted, Source)


The same goes for Source depending on what datatype it is. If the datatype appropriate you could add that one as well before AddedDate in the columns list of the index.

You could even use all the columns from the where clause in the index if like to avoid any residual predicate.

create index IX_TableC_ on TableC(AccountID, Source, Processed, Deleted, AddedDate)


Just make sure AddedDate is the last column in the index and that you don't have any columns before AddedDate that is not used in the where clause.

A blog post by Itzik Ben-Gan on the same lookup problem (for offset-fetch):

Avoid Unnecessary Lookups when Using ROW_NUMBER for Paging