CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017sqlperformance
Niteesh Kumar
Niteesh Kumar

Reputation: 213

how to get all row count of a table from rowcount after having top row filter

I have a huge table in my database and a stored procedure accessing it, which needs pagination.

To achieve this I want total records of the table, and for that, I am facing performance issue because for doing that I need to run this query twice:

Is there any way I can avoid the first query for getting the total count instead of I can use row count or something else?

Upvotes: 0

Views: 1698

Answers (1)

marc_s
marc_s

Reputation: 754348

One way to do it would be something like this:

SELECT 
    (your list of columns),
    COUNT(*) OVER ()       
FROM 
    dbo.YourTable
ORDER BY
    (whatever column you want to order by)
    OFFSET x ROWS FETCH NEXT y ROWS ONLY;

With the OFFSET / FETCH, you retrieve only a page of data - and the COUNT(*) OVER() will give you the total count of the rows in the table - all in a single query

Upvotes: 5

Related Questions