Optimize Query of Count Less Than I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, say, 2.

Would it be efficient to do something like this?...

if (
    select count(*)
      from (select top (2) *
              from sys.databases) t
   ) &gt; 1
begin
  select 1
end
else
begin
  select 0
end
Yes.

The query in the question would in principal be able to do this more efficiently than the obvious alternative of just doing select count(*) from sys.databases.

For the same reason as EXISTS is generally preferable to COUNT. It can short circuit.

The TOP underneath the aggregate operator can stop requesting rows as soon as the second one is received rather than requesting more and causing additional irrelevant rows to be read.



It isn't absolutely guaranteed to be more efficient however. There are occasionally edge cases where adding a TOP causes a worse overall plan than that for the query which simply brings back the whole result set.