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

Recently I’m working on a Social Media WebApp Project in which users can share images, feed videos, etc. And We are showing users post on a page just like Facebook.

For improving performance, I need to implement the pegging in the SQL server. I have completed my task successfully after that I have decided to share the best way according to performance-wise to paginate results in SQL Server.we can do this in many ways in this post we will discuss some techniques to do this.

Best Sql server query with pagination and count

if you are using MSSQL 2012 or the latest version, then you can use Offset and Fetch which is the best way to handle the pegging in the 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; Key points to consider when using it:
  • ORDER BY is necessary to use when using the OFFSET and FETCH clause.
  • OFFSET clause is necessary with FETCH.
  • TOP cannot be used with OFFSET and FETCH.

if you have lower version of sql server you can use below query, the following query would be 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

Please share your thought!

The post [Solved]-Best Sql server query with pagination and count appeared first on Software Development | Programming Tutorials.



Read More Articles