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?