Difference between NOT EXISTS and COUNT(*)
<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>