[Solved]-Best Sql Server Query with Pagination and Count

 In this article, we will learn how to select records from a table with pagination and count.

Recently, I've been working on a Social Media WebApp project where users can share images, videos, and other content. Similar to platforms like Facebook, we display user posts on a page.

To enhance performance, I needed to implement pagination in SQL Server. After successfully completing this task, I decided to share the most efficient methods for paginating results in SQL Server. There are multiple approaches to achieve this, and in this post, we will discuss some of these techniques.

If you are using MSSQL 2012 or a later version, you can utilize Offset and Fetch, which is considered the best way to implement pagination in SQL Server.

SELECT * FROM NewsFeed ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Or

declare @SkipRecord int = 25,
        @TakeRecord int = 100,
        @count int = 0

;WITH Feed_cte AS (
    SELECT Id,Title
    FROM dbo.NewsFeed
)

SELECT 
    Id,
    Title,
    Feedscount.CountFeed AS TotalRows
FROM Feed_cte
    CROSS JOIN (SELECT Count(*) AS CountFeed FROM Feed_cte) AS Feedscount
ORDER BY Id
OFFSET @SkipRecord ROWS
FETCH NEXT @TakeRecord ROWS ONLY; 

• ORDER BY is necessary when using the OFFSET and FETCH clauses.
• The OFFSET clause must be used in conjunction with FETCH.
• TOP cannot be used with OFFSET and FETCH.
• If you have a lower version of SQL Server, you can use the following query, which would be the most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY CreatedAt ) AS RowNum, *
          FROM      NewsFeed
          WHERE     CreatedAt >= '2018-01-01'
        ) AS RowResult
WHERE   RowNum >= 1
    AND RowNum <= 50
ORDER BY RowNum

Above query return rows 1-50 of the original query. Using this query you only have to keep any state the row numbers to be returned.

In SQL server 2000 we don’t have ROW_NUMBER() we can assume ROW_NUMBER() using a table variable with an IDENTITY column.

DECLARE @pageNo int 
DECLARE @pageSize int
SET @pageNo = 2--page number of the webpage
SET @pageSize = 10 ---no of records in one page

DECLARE @firstrow int
DECLARE @lastrow int
SET @firstrow = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastrow = @firstrow + @pageSize - 1   -- 1020

DECLARE @feedKeys TABLE (
  rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
  TableKey int NOT NULL
)

SET ROWCOUNT @lastrow
INSERT INTO @feedKeys (TableKey) SELECT ID FROM NewsFeed WHERE CreatedAt >= '2000-01-01' ORDER BY CreatedAt

SET ROWCOUNT 0

SELECT t.*
FROM NewsFeed t
  INNER JOIN @feedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstrow
ORDER BY o.rownum

This T-SQL script is designed for implementing pagination in SQL Server. 

Declaring variables:@pageNo and @pageSize are declared to hold the page number and the number of records per page, respectively.

Calculating first and last rows for the page:

• @firstrow is calculated by multiplying the page number minus 1 by the page size and adding 1.
• @lastrow is calculated by adding the @pageSize to @firstrow and subtracting 1.

Creating a temporary table to store keys:

• @feedKeys table is created with two columns: rownum to hold the row number and TableKey to store the ID of the records.
• The IDENTITY property is used to automatically generate sequential row numbers.

Filling the temporary table with relevant IDs:

• The SET ROWCOUNT @lastrow statement limits the number of rows affected by the following INSERT statement to @lastrow.
• The INSERT INTO @feedKeys (TableKey) SELECT ID FROM NewsFeed WHERE CreatedAt >= '2000-01-01' ORDER BY CreatedAt statement selects ID values from the NewsFeed table where the CreatedAt column is greater than or equal to '2000-01-01', orders them by CreatedAt, and inserts them into the @feedKeys table.
• The SET ROWCOUNT 0 statement resets the row count to its default value (no limit).

Retrieving the records for the specified page:

• The main SELECT statement retrieves data from the NewsFeed table.
• It joins with the @feedKeys table on the ID column.
• The WHERE clause filters rows based on the rownum column to include only the rows within the specified page range.

Finally, the result set is ordered by the rownum column.

This sql query efficiently retrieves records for a specified page by limiting the number of rows processed using the SET ROWCOUNT statement and efficiently utilizing an indexed temporary table to hold the keys for the relevant records. It's a common approach for pagination in SQL Server, especially when dealing with large datasets.

Please share your thought!