Including count of a result in the main query?

Question

Currently, we execute two queries to get the count and the result with a pagination filter. While we can easily combine these into a single network call (using SQL separators), is there a way to do this in a single query following the DRY principle? It is also challenging maintain both queries - single query reduces the overhead.

-- count    
SELECT COUNT(*) FROM table;

-- result with pagination
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
WHERE row >= 1 AND row <= 10;

Answer 1

You can try create a stored procedure and call it from the application.

CREATE PROCEDURE [dbo].[getPage] 
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Or you can use subquery:

CREATE PROCEDURE [dbo].[getPage] 
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY tbl.idn) AS row, * FROM tbl)_tbl
    WHERE row >= 1 AND row <= 10;
END

Edit.

Even better you can use OFFSET FETCH clause for paging puprose. You will get much cleaner code and higher performance (no window function here). For example somthing like this:

CREATE PROCEDURE [dbo].[getPage] 
AS
BEGIN
    DECLARE @cnt INT = (SELECT COUNT(*) FROM table)

    SELECT *, [cnt] = @cnt
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

Or with subquery:

CREATE PROCEDURE [dbo].[getPage] 
AS
BEGIN
    SELECT *, [cnt] = (SELECT COUNT(*) FROM table)
    FROM tbl
    ORDER BY idn
    OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
END

There is no significant diffrence between a subquery and a variable approach. It's more about the notation. SQL Server does almost the same work (additional "nocost" Nested Loop). In this particular sitiation I prefer to avoid window function (possible Table Spool - additional impact on tempdb).