[SOLVED]-BEST SQL SERVER QUERY WITH PAGINATION AND COUNT
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:
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 RowNumAbove 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