Results 1 to 6 of 6

Thread: EXISTS() vs. SELECT COUNT(*) vs. EXISTS (SELECT *)

  1. #1
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92

    EXISTS() vs. SELECT COUNT(*) vs. EXISTS (SELECT *)

    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?

  2. #2
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92
    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.

  3. #3
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92
    If nobody can explain what I'm seeing here, can someone try it and let me know if they see the same result.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    In other words, the second query is not logically same as other queries.

  6. #6
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92
    Thanks for that. Obvious really, wasn't it. (Not that I thought of it!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •