# Database Discussions > Microsoft SQL Server 2005 >  EXISTS() vs. SELECT COUNT(*) vs. EXISTS (SELECT *)

## bobbo

I was reading about optimizing EXISTS today and decided to try a couple of variations on this theme. In this example, all I want to know is does the ID exist in the table. I presumed the following would all work exactly the same:

Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22

IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)
	print 'exists 1'

IF EXISTS(Select * From Jurisdiction WITH (NOLOCK) Where JID= 22)
	print 'exists 2'

IF EXISTS(Select 1 From Jurisdiction WITH (NOLOCK) Where JID= 22)
	print 'exists 3'

To test, I turned on 'Include Actual Execution Plan' and ran.
To my surprise, the first, third and fourth queries each took 33% of the total but the second took zero. They all return the correct result - the first query returns '1', the rest print 'exists x'. 
In the execution plan for the first, third and fourth queries,  100% of the time taken for each query is attributed to a clustered index seek. The second query only consists of a Constant scan. 
Can you explain this? Is it using something in memory that it's not showing?

----------


## bobbo

I have tried this using a different table in a different database on a different server just for giggles. This table has no indexes. Exactly the same result except I get a Table scans instead of clustered index seeks.

----------


## bobbo

If nobody can explain what I'm seeing here, can someone try it and let me know if they see the same result.

----------


## skhanal

I think 

IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)

does not need to query anything from the table because even if count(*) is 0 it always returns a resultset, exists returns true. That is true for any kind of grouping operator like max, min, sum.

So it is always true.

Running these on master database 

Select * From sysobjects WITH (NOLOCK) Where ID= 22
Select count(*) From sysobjects WITH (NOLOCK) Where ID= 22


name                                                                                                                             id          xtype uid    info   status      base_schema_ver replinfo    parent_obj  crdate                  ftcatid schema_ver  stats_schema_ver type userstat sysstat indexdel refdate                 version     deltrig     instrig     updtrig     seltrig     category    cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------

(0 row(s) affected)


-----------
0

(1 row(s) affected)

Second query returned a row with 0 as count, thus exists is always true, it is not looking for count(*) value but rows returned value.

----------


## skhanal

In other words, the second query is not logically same as other queries.

----------


## bobbo

Thanks for that. Obvious really, wasn't it. (Not that I thought of it!)

----------

