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?

Solution

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

Comments

[Igor Borisenko]

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

[jennykwan]

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]

@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.

[jean]

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