IF EXISTS (SELECT..) vs. IF (SELECT COUNT(1)..) > 0


Consider this a coding tip for SQL Server 2000 (as well as a reason to upgrade), and kudos for the SQL Server 2005 development team. 


In SQL Server 2000, IF (SELECT COUNT(1)..) > 0 will process an entire table or index to complete the count, while IF EXISTS (SELECT..) will stop processing data when it finds the first row.  This behavior is documented here, among other places.


This behavior has changed in SQL Server 2005.  The COUNT syntax is now “converted” to an EXISTS test by the optimizer, and behaves as such.


Those of you with poorly performing COUNTs in SQL Server 2000 now have another arrow in your quiver..


     -wp

Comments (6)

  1. Sunil kumar says:

    Exists is much better than count

  2. Joe says:

    But can you do this…

    IF EXISTS

    (

    EXEC sp_1

    )

    EXEC sp_2

    ?

  3. Balaji Birajdar says:

    Please elaborate the concept more clearly.

  4. Bharath Reddy VasiReddy says:

    This article is Very nice one..thanks

  5. VIBHAVESH says:

    IF  EXISTS(

    SELECT name

    FROM sys.databases

    WHERE name = ‘HCL,ORACLE_DBA,HCL’

    );

    WHICH TYPE OF ERRORS

  6. Kevin says:

    I tried this to check whether data was present for a user-defined table being passed into a stored proc and even though the count was 0, "if exists" returned true.