EXISTS vs. COUNT(*)

Question: I'm running SQL Server 2000 and need to see whether a particular value exists in a table. Which is more efficient, using an EXISTS check or a SELECT COUNT(*)?

Answer: Using the T-SQL EXISTS keyword to perform an existence check is almost always faster than using COUNT(*). EXISTS can stop as soon as the logical test proves true, but COUNT(*) must count every row, even after it knows one row has passed the test. Let's walk through a simple example to illustrate this point.

Run the following SELECT statement to create a test table based on the OrderDetails table from Northwind:

SELECT * INTO tempdb..OrderDetails FROM [Northwind]

To keep the example simple, I didn't create any indexes on this table. Now run the following code, which uses EXISTS to check whether a particular value exists in a table:

SET STATISTICS IO ONIF EXISTS(SELECT * FROM OrderDetailsWHERE orderid = 10248)PRINT 'yes'ELSEPRINT 'no'SET STATISTICS IO OFF

The code passes the test, giving you the following STATISTICS IO information:

Table 'OrderDetails'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Now run the following code, which uses COUNT(*) to check whether a particular value exists in a table:

SET STATISTICS IO ONIF (SELECT COUNT(*) FROM OrderDetailsWHERE orderid = 10248) > 0PRINT 'yes'ELSEPRINT 'no'SET STATISTICS IO OFF

This code also passes the test. However, you'll see the following STATISTICS IO information:

Table 'OrderDetails'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.

In this simple example on a small table, the EXISTS check found the row right away, limiting the search to two logical reads. The COUNT(*) check performed 10 logical reads. In many cases, the performance difference between an EXISTS check and COUNT(*) is even more pronounced.

Related: T-SQL 101, Lesson 1