title: Difference between NOT EXISTS and COUNT(*)
body: <p>I'm getting a different response between two statements that I would consider to be equivalent: <code>SELECT COUNT(*)</code> vs <code>NOT EXISTS</code>. In ~49 out 50 executions these two statements behave the same, but occasionally they do not. </p>
  
  <p>My guess is there is a difference between how these statements handle locked pages or something maybe something more 'far out'. My question is what is that difference?</p>
  
  <p>I originally added some logging (STATEMENT 3) to my SPROC which is how I saw they had different values. Since the contents of <code>TableWithResults</code> can change while this SPROC is executing, is I added STATEMENT #3 so I could confirm the result of <code>COUNT(*)</code> before and after the <code>NOT EXISTS</code>:</p>
  
  <pre><code>-- STATEMENT 1: If this is returning 1...
  SET @MatchingResultCount1 = ( SELECT COUNT (*) FROM TableWithResults  WHERE  SearchId = @SearchId ) 
  
  -- STATEMENT 2: ...I expect this to evaluate "False" and sets IS_VALID='Y'
  IF NOT EXISTS ( SELECT * FROM TableWithResults WHERE  SearchId = @SearchId )
     SET @IS_VALID = 'N';
  ELSE
     SET @IS_VALID = 'Y';
  
  -- STATEMENT 3: This is to confirm the table didn't change
  SET @MatchingResultCount1 = ( SELECT COUNT (*) FROM TableWithResults  WHERE  SearchId = @SearchId ) 
  </code></pre>
  
  <p>What I'm seeing is <code>@MatchingResultCount1</code> and <code>@MatchingResultCount2</code> are 100% always the same. <em>Almost</em> all the time <code>@IS_VALID = 'Y'</code> when <code>@MatchingResultCount1 &gt; 0</code>, but 1/50 times, <code>@IS_VALID = 'N'</code> when <code>@MatchingResultCount1 &gt; 0</code>.</p>
  
  <p>I tried using <code>WITH (NOLOCK)</code> on all statements and that didn't seem to make a difference.</p>