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 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:

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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More