How do you Select TOP x but still get a COUNT of the whole query?

Question

I'm writing a webpage to interactively filter results based on filter criteria as it is specified by the user. I only want to return from SQL the TOP 20 rows but I want to know how many rows met the criteria (Count). I want to be able to tell the user: "here are the top 20 rows matching your criteria, and BTW, there were 2,000 additional rows I'm not showing here".

I know I could simply run the query twice but EWWWW that's expensive and wasteful. How can I achieve what I want without over taxing the database?

Answer 1

You can use COUNT(*) OVER()

SELECT TOP 20 *, 
       COUNT(*) OVER() AS TotalMatchingRows
FROM master..spt_values
WHERE type='P'
ORDER BY number

Doing two queries may work out more efficient however especially if you have narrower indexes that can be used in determining the matching row count but don't cover the entire SELECT list.