Anyway to get a value similar to @@ROWCOUNT when TOP is used?

Question

If I have a SQL statement such as:

SELECT TOP 5 
* 
FROM Person 
WHERE Name LIKE 'Sm%'
ORDER BY ID DESC

PRINT @@ROWCOUNT
-- shows '5'

Is there anyway to get a value like @@ROWCOUNT that is the actual count of all of the rows that match the query without re-issuing the query again sans the TOP 5?

The actual problem is a much more complex and intensive query that performs beautifully since we can use TOP n or SET ROWCOUNT n but then we cannot get a total count which is required to display paging information in the UI correctly. Presently we have to re-issue the query with a @Count = COUNT(ID) instead of *.

Answer 1

you'll have to run another COUNT() query:

SELECT TOP 5 
* 
FROM Person 
WHERE Name LIKE 'Sm%'
ORDER BY ID DESC

DECLARE @r int
SELECT
@r=COUNT(*)
FROM Person 
WHERE Name LIKE 'Sm%'

select @r

Answer 2

Whilst this doesn't exactly meet your requirement (in that the total count isn't returned as a variable), it can be done in a single statement:

;WITH rowCTE
AS
(
 SELECT * 
        ,ROW_NUMBER() OVER (ORDER BY ID DESC) AS rn1 
        ,ROW_NUMBER() OVER (ORDER BY ID ASC)  AS rn2
 FROM Person 
 WHERE Name LIKE 'Sm%'
)
SELECT *
       ,(rn1 + rn2) - 1 as totalCount
FROM rowCTE
WHERE rn1 <=5

The totalCount column will have the total number of rows matching the where filter.

It would be interesting to see how this stacks up performance-wise against two queries on a decent-sized data-set.

Answer 3

Something like this may do it:

SELECT TOP 5  
*  
FROM Person  
 cross join (select count(*) HowMany
              from Person
              WHERE Name LIKE 'Sm%') tot
WHERE Name LIKE 'Sm%' 
ORDER BY ID DESC 

The subquery returns one row with one column containing the full count; the cross join includes it with all rows returned by the "main" query"; and "SELECT *" would include new column HowMany.

Depending on your needs, the next step might be to filter out that column from your return set. One way would be to load the data from the query into a temp table, and then return just the desired columns, and get rowcount from the HowMany column from any row.