none
IF EXISTS in SQL - Performance Quotient

    Question

  • If we have a SELECT statement inside an IF EXISTS, does the execution stops as soon as it finds a record in the table? For an e.g.

     

    IF EXISTS(SELECT *

                    FROM   table1

    WHERE Name='John' )

    return 1

    else

    return 0

    If a row exists in the table with the name =John, does it stops execution and returns 1 or does it traverses through the entire table looking for more matches?

     

     

    Wednesday, September 07, 2011 12:31 AM

Answers

All replies

  • It stops when it finds the first one.
    Tom G.
    Wednesday, September 07, 2011 12:52 AM
  • Hi,

     

    As @Tom said: It stops when it finds the first one.

    You can also try it at your managment studio, to varified the result.

     

     



    Regards, Ryan Lambatan
    Please "Mark as Answer" or "Vote as Helpful"
    Wednesday, September 07, 2011 5:42 AM
  • @Tom and @Ryan,

    Is there a better way to do the following:                        

    IF EXISTS(SELECT * FROM t1 WHERE xxx AND yyy) 

            RETURN 2 

    ELSE 

            BEGIN 

                IF EXISTS(SELECT * FROM t1 WHERE xxx) 

                  RETURN 1 

                ELSE 

                  RETURN 0 

            END                                                                  

    I have this inside a stored proc and the stored proc gets executed very often. My primary motivation is to improve the performance. I know I can use a constant 1 or 0 instead of a * in SELECT but the corresponding performance improvement is negligible


    SP
    Wednesday, September 07, 2011 3:32 PM