Does SQL Server TOP stop processing once it finds enough rows?

Question

When you use the SQL Server TOP clause in a query, does the SQL Server engine stop searching for rows once it has enough to satisfy the TOP X needed to be returned?

Consider the following queries (assume some_text_field is unique and not set for full-text indexing):

SELECT
    pk_id
FROM
    some_table
WHERE
    some_text_field = 'some_value';

and

SELECT TOP 1
    pk_id
FROM
    some_table
WHERE
    some_text_field = 'some_value';

The first query would need to search the entire table and return all of the results it found. The way we have it setup though, that query would ever really return one value. So, would using TOP 1 prevent SQL server from scanning the rest of the table once it has found a match?

Answer 1

Yes, the query stops once it has found enough rows, and doesn't query the rest of the table(s).

Note however that you would probably want to have an index that the database can use for the query. In that case there isn't really any performance difference between getting the first match and getting all one matches.

Answer 2

Yes.

In this case you would get 1 undefined row (as TOP without ORDER BY doesn't guarantee any particular result) then it would stop processing (The TOP iterator in the plan won't request any more rows from child iterators).

If there is a blocking operator (such as SORT) in the plan before the TOP operator or parallel operators before the TOP it may end up doing a lot of work for rows not returned in the final result anyway though.