Efficient way to check if a SQL query will return results

Question

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 1

This is the fastest i could get in my projects:

SELECT CASE WHEN EXISTS (   select top 1 1    from myTable    where id=7    and rowInsertDate BETWEEN '01/01/2009' AND GETDATE() ) THEN 1 ELSE 0 END AS AnyData 

Answer 2

Do you have an index on id and date?

maybe you just want:

select top 1  1 from myTable where id=7 and rowInsertDate > '01/01/2009'  

note: this would return 1 if data exists, or nothing otherwise.

another edit. This won't return a row with the value null if there is no data, but rather will not return any rows. More like null in its more figurative sense.