Efficiently checking if more than one row exists in a recordset

Question

I'm using SQL Server 2008 R2, and I'm trying to find an efficient way to test if more than 1 row exists in a table matching a condition.

The naive way to do it is a COUNT:

IF  (   SELECT  COUNT(*)
        FROM    Table
        WHERE   Column  = <something>
    )   > 1 BEGIN
    ...
END

But this requires actually computing a COUNT, which is wasteful. I just want to test for more than 1.

The only thing I've come up with is a COUNT on a TOP 2:

IF  (   SELECT  COUNT(*)
        FROM    (   SELECT  TOP 2   0   x
                    FROM    Table
                    WHERE   Column  = <something>
                )   x
    )   > 1 BEGIN
    ...
END

This is clunky and requires commenting to document. Is there a more terse way?

As in, does the table have 2 rows matching my search condition. SQL Server has efficient syntax for checking if any rows exist - use EXISTS. I'm looking for an expression to check if the table has more than one, without computing a COUNT over the whole set, which is unnecessarily expensive. — jennykwan, Mar 13 '13 at 15:17
@Pete OP wants to know if there are two or more rows that satisfy given condition and wants to do it in most efficient/concise manner. — Nikola Markovinović, Mar 13 '13 at 15:17
>>But this requires actually computing a COUNT, which is wasteful<< Look into execution plan to see how much resources `COUNT` takes and you will realize that this problem is out of nothing :) — Igor Borisenko, Mar 13 '13 at 15:19
I'm not concerned about the COUNT operation itself. It's the IO to actually scan the rows. Assuming the worst case of a column with no index, I would want a full table scan that quits after it hits the second row. — jennykwan, Mar 13 '13 at 15:21
@Pete Well, based on the plans I'm staring at, COUNT on a TOP 2 *is* more efficient, because it will short circuit. Also, 70 upvotes on a question that is irrelevant is still irrelevant. — jennykwan, Mar 13 '13 at 15:28

score 1 · Answer 1 · answered Mar 13 '13 at 15:33

If you have a PK in the table that you're checking for >1 row, you could nest another EXISTS clause. Not sure if this is faster, but it achieves your record result. For example, assuming a Station table with a PK named ID that can have zero-to-many Location table records with a PK named ID, Location has FK StationID, and you want to find the Stations with at least two Locations:

SELECT s.ID
FROM Station s
WHERE EXISTS (
    SELECT 1
    FROM Location L
    WHERE L.StationID = s.ID
    AND EXISTS (
        SELECT 1
        FROM Location L2
        WHERE L2.StationID = L.StationID
        AND L2.ID <> L.ID
    )
)

score 0 · Answer 2 · answered Sep 22 '16 at 14:42

0

I have the following solution to share, which may be lighter performance-wise. I suppose that you are trying to fetch the first record and process it once you make sure that your SQL selection returns a single record. Therefore, go on and fetch it, but once you do that, try to fetch the next record right away, and if successful, you know that more than one record exists, and you can start your exception processing logic. Otherwise, you can still process your single record.

answered Sep 22 '16 at 14:42

Andrej Zabrodsky

1

It's can be fine in a full imperative-procedural solution (C#, Java, PHP) but not good in a set paradigma (SQL) (and please don't even mention cursors/rbar). If your app is working this way it must have a horrible DB round-trip efficience – jean Sep 22 '16 at 16:26

Efficiently checking if more than one row exists in a recordset

2 Answers2