Preserving Total Result Information While Paginating In SQL Server

I'm trying to do pagination in a stored procedure in SQL Server, like this:

/*Assign a row number to each row*/
SELECT
ROW_NUMBER() OVER (ORDER BY A, B, C ASC) AS ROW_NUMBER,
A, B, C
FROM ABC
WHERE ROW_NUMBER BETWEEN @startRecordNumber and @endRecordNumber

But, my calling code also wants to know how many results were in the original results set before pagination. So, I change my code to this:

/*Put the results into a temp table first*/
SELECT
ROW_NUMBER(开发者_如何学编程) OVER (ORDER BY A, B, C ASC) AS ROW_NUMBER,
A, B, C
INTO #TOTAL_RESULTS
FROM ABC

/*Get the total results from @@RowCount*/
declare @totalResults bigint
set @totalResults = @@RowCount

/*Now just get the desired page from the temp table*/
SELECT
A, B, C
FROM #TOTAL_RESULTS
WHERE ROW_NUMBER BETWEEN @startRecordNumber and @endRecordNumber

DROP TABLE #TOTAL_RESULTS

This feels pretty roundabout to me. Is there any way to get the size of the original result set without having to make a temp table? Maybe just a Common Table Expression instead? I can't seem to figure out a way to do it.

In case it matters, here is ABC's schema:

ABC

A (PK, smallint not null)

B (PK, smallint not null)

C (PK, smallint not null)

You can use windowed aggregate functions. i.e COUNT(*) OVER() example below.

;WITH cte As
(
SELECT *, 
        ROW_NUMBER() OVER (ORDER BY number) AS RN,
        COUNT(*) OVER() AS Cnt
FROM master..spt_values
)
SELECT *
FROM cte 
WHERE RN BETWEEN 101 and 200

Or- Not really a serious suggestion but does avoid spools, work tables and double sorts :-)

DECLARE @Spid INT = @@Spid
DECLARE @TraceID INT

DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 146, 1, 1
exec sp_trace_setevent @TraceID, 146, 22, 1
exec sp_trace_setevent @TraceID, 146, 34, 1
exec sp_trace_setevent @TraceID, 146, 51, 1
exec sp_trace_setevent @TraceID, 146, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1


;WITH cte AS
(
SELECT number, type, name, 
        ROW_NUMBER() OVER (ORDER BY number, type, name) AS RN
FROM master..spt_values
)
SELECT * FROM cte
WHERE RN BETWEEN 101 AND 200
OR RN+1 =0 /*To stop a "TOP 200" getting added to the plan*/

;WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
     SELECT  ActualRows
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@ActualRows', 'INT') AS ActualRows
                             FROM   xPlan.nodes('//sql:RelOp[@LogicalOp="Segment"]/sql:RunTimeInformation/sql:RunTimeCountersPerThread') T(relop)) ca
         WHERE  property = 2
                AND ObjectName<>'fn_trace_getinfo' AND TextData NOT LIKE '%ThisQuery%'  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2

As far as I know, it is not possible both to return rows and to assign variables at the same time with SQL Server. So if you want the row count to be stored into a variable, you'll have to have more than one statement one way or the other.

But if it is fine to return the total as a column and if there are no duplicates in the table by the A, B & C columns, you could return the total, for example, like this:

SELECT
  A, B, C,
  TotalResults = RowNumAsc + RowNumDesc - 1
FROM (
  SELECT
    A, B, C,
    RowNumAsc = ROW_NUMBER() OVER (ORDER BY A, B, C),
    RowNumDesc = ROW_NUMBER() OVER (ORDER BY A DESC, B DESC, C DESC)
  FROM
) s
WHERE RowNumAsc BETWEEN @startRecordNumber AND @endRecordNumber