SELECT WITH (NOLOCK) sometimes returns no results

Question

The following query usually returns a large number of results (somewhere around ~750k results). However, in production, I encounter a sporadic situation where it returns 0 results. If we were to re-run it the next second after it returned 0, it would just return the results as normal.

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this?

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name
FROM dbo.Table1 WITH (NOLOCK)
INNER JOIN dbo.Table2 WITH (NOLOCK)
  ON Table1.id = Table2.id
  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'
  AND  Table1.some_boolean_1 = 1
  AND  Table1.some_date_1 IS NULL
  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)
  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 
       Table2.some_date_3 > '20171119')
GROUP BY Table1.Email

Many thanks!

Answer 1

The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way, meaning data integrity is ignored, and results can be any kinds of intermediate state. Same effect as setting the transaction isolation level to READ UNCOMMITTED, but for this query only. Results can include also inserted, but not yet committed rows, and anything running besides a transaction. Not sure what causes the empty result, but it can return strange results when modifications run at the same time.