16

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@w.cn') > 0
    print 'Exist'
else
    print 'Not Exist'

OR

if Exists(Select id from Registeration where email='email@w.cn') 
    print 'Exist'
else
    print 'Not Exist'
gbn
  • 422,506
  • 82
  • 585
  • 676
Amr Badawy
  • 7,453
  • 12
  • 49
  • 84

2 Answers2

28

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    +1. However, in the simplified example, if `email` is indexed, the difference should be so minimal as to be unmeasurable. – Toby Jul 17 '10 at 12:48
4

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

Hotmoil
  • 623
  • 1
  • 7
  • 15
  • 1
    yes, if you have more than 2 billion rows, then you should use `count_big`, or your RBMS' equivalent. – Donnie Jul 17 '10 at 15:07