exist (select * from my table)

Question

My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.

Query (1):

IF ( SELECT COUNT(UnitTrustCounterId)
     FROM   UnitTrustCounter
     WHERE  PeriodId = 0
   ) > 0 
    SELECT  1
ELSE 
    SELECT  0  

Query (2):

IF EXISTS ( SELECT  *
            FROM    UnitTrustCounter
            WHERE   PeriodId = 0 ) 
    SELECT  1
ELSE 
    SELECT  0   

Answer 1

Well I would like to comment on further aspect of using if-else with exists.

It's best to avoid using if-else with exists. Why?

1. You need to make sure at least one record exists in your table
2. This method does not allow for concurrency (specially in insert, updates transactions, it could end up creating deadlock)

Instead you may use WHERE EXISTS

Reference materials that explains well: avoid if else with exists in sql

And to prove your colleagues wrong ;) check this article out too. I am not going to re-write Author's data here. But the tag line is worth re-writing again and again.

" The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. "

e.g. from article

if exists (select *
    from authors
    where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
    from authors
    where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table. Even with that small number of records, the IF EXISTS version runs 4 times faster than selecting a count. This is because it stops as soon as it finds a single record that matches the criteria. The second statement must process all the rows that match. --<<--taboo :D

Reference : using if exists vs if count(*)