Efficient way to check if a SQL query will return results

user_27412 0
Viewed 37428

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

sql
10 Answers

I would just return select exists instead of making it an int

SELECT EXISTS(SELECT 1 FROM MY_TABLE);

It will return:

Select result, showing single row result with column exists being true

If you need a custom name then you can do

SELECT EXISTS(SELECT 1 FROM MY_TABLE) AS MY_CUSTOM_NAME;
user_108803570 
select isnull(
(select 1 from myTable 
  where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE())
,0)
user_120162490
Related