Partager via

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

  • Article

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

  • Anonymous
    April 29, 2008
    Exists is much better than count

  • Anonymous
    May 20, 2008
    But can you do this... IF EXISTS ( EXEC sp_1 ) EXEC sp_2 ?

  • Anonymous
    January 11, 2010
    Please elaborate the concept more clearly.

  • Anonymous
    February 24, 2010
    This article is Very nice one..thanks

  • Anonymous
    February 26, 2010
    IF  EXISTS( SELECT name FROM sys.databases WHERE name = 'HCL,ORACLE_DBA,HCL' ); WHICH TYPE OF ERRORS

  • Anonymous
    October 10, 2011
    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.