What's the best to check if item exist or not: Select Count(ID)OR Exist(...)?

Question

What's the best in performance to determined if an item exist or not specially if the table contain more than 700,000 row

if (Select count(id) from Registeration where email='[email protected]') > 0
    print 'Exist'
else
    print 'Not Exist'

OR

if Exists(Select id from Registeration where email='[email protected]') 
    print 'Exist'
else
    print 'Not Exist'

Answer 1

EXISTS, always

• COUNT will traverse the table or an index: you asked for a COUNT
• EXISTS will stop as soon as it finds a row

Edit, to be clear

Of course, in this case if the email column is unique and indexed it will be close.

Generally, EXISTS will use less resources and is more correct too. You are looking for existence of a row, not "more than zero" even if they are the same

Edit2: In the EXISTS, you can use NULL, 1, ID, or even 1/0: it isn't checked...

21 May 2011 edit:

It looks like this was optimised in SQL Server 2005+ so COUNT is now the same as EXISTS in this case

Answer 2

also take in consideration that Count() only return int in which if you count some data that exceed int it will generate error