Reduce Query Resource Use - SQL Server Query Performance Tuning - page 373

Use EXISTS over COUNT(*) to Verify Data Existence
A common database requirement is to verify whether a set of data exists. Usually you'll see this implemented using a
batch of SQL queries, as follows ( --count in the download):
DECLARE @n INT ;
SELECT @n = COUNT(*)
FROM Sales.SalesOrderDetail AS sod
WHERE sod.OrderQty = 1;
IF @n > 0
PRINT 'Record Exists';
Using COUNT(*) to verify the existence of data is highly resource-intensive, because COUNT(*) has to scan all the
rows in a table. EXISTS merely has to scan and stop at the first record that matches the EXISTS criterion. To improve
performance, use EXISTS instead of the COUNT(*) approach.
IF EXISTS ( SELECT sod.*
FROM Sales.SalesOrderDetail AS sod
WHERE sod.OrderQty = 1 )
PRINT 'Record Exists';
The performance benefit of the EXISTS technique over the COUNT(*) technique can be compared using the
STATISTICS IO and TIME output, as well as the execution plan in Figure 19-2 , as you can see from the output of
running these queries.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246
CPU time = 0 ms, elapsed time = 29 ms.
Table 'SalesOrderDetail'. Scan count 1, logical reads 3
CPU time = 0 ms, elapsed time = 4 ms.
Figure 19-2. Difference between COUNT and EXISTS