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