Efficient way to check if a SQL query will return results

I would like to write a query that simply returns 1 or 0 depending if there will be results.

I am thinking to use this

IF EXISTS(
      select * from myTable 
      where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0

That’s the general premise.

The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql

My question is lets say the ‘count’ would return 376986 and takes 4 seconds to calculate. Is using the IF EXISTS going to stop as soon as it find 1 row that satisfies the criteria.

I’m deciding wether to use IF EXISTS or just query the @@ROWCOUNT and see if it is greater than zero.

I did try some tests and both pretty much ran at the same speed but in 2 years time when there’s alot more data is it likely using IF EXISTS is going to be a performance gain or not?

Thanks

Answer

IF EXISTS should be more efficient, because it is optimised to stop as soon as it find the first row. This is how I would always do this kind of check, not using a COUNT().

For performance comparison, just ensure you are testing fairly by clearing down the data and execution plan caches (non-production db server only) before each test:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS