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 ON IF EXISTS(SELECT * FROM OrderDetails WHERE orderid = 10248) PRINT 'yes' ELSE PRINT '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 ON IF (SELECT COUNT(*) FROM OrderDetails WHERE orderid = 10248) > 0 PRINT 'yes' ELSE PRINT '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
