Including count of a result in the main query? Currently we execute two queries to get the count and result with a pagination filter. While we can easily combine both of these into a single network call (using sql seprators), is there a way to do this in a single query following the DRY principle?



-- 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 &gt;= 1 AND row &lt;= 10;


It is really challenging to maintain both queries - single query reduces the maintenance overhead.
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 &gt;= 1 AND row &lt;= 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 &gt;= 1 AND row &lt;= 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).