@mbhandari: Glad we could help!

Life got pesky, so it took me longer than I wanted to post the comparison of the IF EXISTS and IF COUNT(*)>0 queries, but here it is.

--Create our wonderful test table

CREATE TABLE ExistsVSCount (

SomeNumber int

)

GO

--Populate our test table with 100,000 instances of each of the integers from 1 to 10

WITH N (SomeNumber) AS (

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5

UNION ALL

SELECT 6

UNION ALL

SELECT 7

UNION ALL

SELECT 8

UNION ALL

SELECT 9

UNION ALL

SELECT 10

)

INSERT INTO ExistsVSCount (SomeNumber)

SELECT N1.SomeNumber

FROM N AS N1

CROSS JOIN N AS N2

CROSS JOIN N AS N3

CROSS JOIN N AS N4

CROSS JOIN N AS N5

CROSS JOIN N AS N6

--Let's check the number of pages in our newly populated heap. For me it says we have 3,345 data pages.

--We'll remember that for later.

SELECT au.data_pages FROM sys.tables AS t

INNER JOIN sys.indexes AS i ON t.object_id=i.object_id

INNER JOIN sys.partitions AS p ON i.object_id=p.object_id

INNER JOIN sys.allocation_units AS au ON au.container_id=p.partition_id

WHERE t.name='ExistsVSCount'

--Make sure we see the glorious IO stats

SET STATISTICS IO ON

--Check for the existence of values larger than 8 using IF EXISTS and SELECT TOP

IF EXISTS

(SELECT TOP (1) SomeNumber FROM ExistsVSCount WHERE SomeNumber>8)

SELECT 'There''s something larger than eight!'

--IO stats showed the following:

--Table 'ExistsVSCount'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Check for the existence of values larger than 8 using IF SELECT COUNT(*)...>0

--A terrible idea, because we'll just scan the whole table, right?

IF (SELECT COUNT(*) FROM ExistsVSCount WHERE SomeNumber>8)>0

SELECT 'There''s something larger than eight!'

--IO Stats for this one:

--Table 'ExistsVSCount'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Ok, so those are exactly the same, reading less than 10% of the table.

--The exact number of logical reads for these two will vary a bit if the script is run multiple times, but this run seemed representative.

--Looks like the optimizer was clever enough, as expected.

--Let's now get some confirmation that this is an optimization specifically for IF (SELECT COUNT(*)...>0, by switching that to >1

IF (SELECT COUNT(*) FROM ExistsVSCount WHERE SomeNumber>8)>1

SELECT 'There''s something larger than eight!'

--IO Stats for this one:

--Table 'ExistsVSCount'. Scan count 1, logical reads 3345, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Ah, now we're seeing what we originally thought would happen, and reading all 3,345 data pages in the heap.

--Clean up after ourselves

SET STATISTICS IO OFF

DROP TABLE ExistsVSCount

In short, the optimizer does indeed run them in exactly the same way.

The actual execution plans for each are attached.

The specific example above was run on 2008, but I tested and saw the same behavior on 2005, 2008 R2, 2012, and 2014 as well.

I'd still prefer the IF EXISTS, just because it's actually expressing the more efficient logic, while the COUNT(*)>0 is relying on a trick from the optimizer, but it does seem that they run in exactly the same way.

Cheers!