Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 7
  • Likes

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
  • Exists is much better than count

  • But can you do this...

    IF EXISTS

    (

    EXEC sp_1

    )

    EXEC sp_2

    ?

  • Please elaborate the concept more clearly.

  • This article is Very nice one..thanks

  • IF  EXISTS(

    SELECT name

    FROM sys.databases

    WHERE name = 'HCL,ORACLE_DBA,HCL'

    );

    WHICH TYPE OF ERRORS

  • 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.  

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment