Efficiently get first 10 results and total number of results For a search page, I need the first 10 results and the total number of results of a query.

That part is easy:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT COUNT(*) FROM ... WHERE ...


But it's also amazingly slow. Both statements take about the same time.

So I found this one:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT @@ROWCOUNT


That cuts down the time by ~50%. Great. But unfortunately the rowcount returned is 10. If I remove the "TOP(10)" the rowcount is correct, but the timespan is more or less the same as in the original version. (Even though I only access the first 10 rows.)

That's frustrating, because the total-number-of-lines info must exist, even in the TOP(10) version. (At least I think so, because we use a very complex ORDER BY clause, with 3 "CASE" conditions to sort empty data to the bottom)

Is there an efficient way to get both? TOP(10) data and total number?
If your ORDER BY and WHERE clauses are complicated, then proposed solution should cut time in half:
1. Create a new table for intermittent results (it can be a temp table, table variable or memory optimized table with session id marker and schema_only setting).
2. Insert all rows from source query into this table, including order by expressions as additional columns.
3. Run your count and top 10 query from this new table.