Short circuit count query

Question

I have a stored procedure that searches for rows given a long list of parameters. Some of the queries result in extremely slow count queries. Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count? It's not important that the users know exactly how many rows there are - especially when the result is in the thousands.

Answer 1

Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count?

Sure. A query like this should do the trick:

with q as
(
  select top 100 *
  from …
  where …
) 
select count(*) 
from q