Should I use Top(1) in a SubQuery

Question

Example Query:

select * 
from A join B on A.ID = B.SOMEVALUE
where A.VALUE="something" and
B.ID = 
       (select ID from B where SOMEVALUE = A.ID and 
              THISDATE = (select max(SOMEDATE) from B where ...))

so, if you can read SQL you should see that I am doing a couple correlated subqueries to narrow down the results of the join . (and yes, this is horribly over-simplified).

In certain cases the subquery:

select ID from B where SOMEVALUE = A.ID and 
    THISDATE = (select max(SOMEDATE) from B where ...)

can return more than 1 value, which causes an error

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

which I fully expect. This is obviously not a good thing and I have code in place to (hopefully) prevent these duplicates from getting into the database in the first place (ie table B should only have 1 row that matches the

SOMEVALUE = A.ID and max(SOMEDATE)

criteria), however end-users are nothing if not creative in finding ways I can't think of to break software.

So now to my question:

Would it be better to change the first subquery to

select top 1 * from B ...

to prevent the user from seeing an error when/if (hopefully never) this situation arises or let the error come through. I'm leaning to not adding the top statement and letting the error come through rather then let the user see potentially incorrect data. I'm wondering if anyone has any thoughts on Best Practices in a situation like this...

Answer 1

Normally TOP 1 is a good idea.

Consider a large table with millions of rows with no index on the column you are matching, however you are only looking for a single row.

SELECT TOP 1 will mean the table scan stops as soon as the one item is found.

Without the TOP 1, the table scan will continue right through to the end.

As with anything that involves scanning (or brute force) to do the search. Using TOP 1, it should on average be 50% quicker than not using TOP 1.

However, Depending on what you need to return back, A real performance gain can normally be made by using EXISTS.

Instead of writing

SELECT * FROM table t
WHERE t.id = (SELECT TOP 1 foreignid from table2)

You can use

SELECT * FROM table t
WHERE EXISTS (SELECT 1 from table2 WHERE foreignid = t.id)