Database side pagination

Pagination is a technique used to break up large datasets into smaller chunks, making it easier to display them on screen. This method is often referred to as paging. It is commonly used in applications, and one of the best examples can be seen in Google Search.

Method 1

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount] = COUNT(*) OVER ()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;
GO

Method 2

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

WITH Main_CTE
AS (
 SELECT [SalesOrderID]
  ,[SalesOrderDetailID]
  ,[CarrierTrackingNumber]
  ,[OrderQty]
  ,[ProductID]
  ,[SpecialOfferID]
 FROM [dbo].[SalesOrderDetail]
 )
 ,Count_CTE
AS (
 SELECT COUNT(*) AS [TotalCount]
 FROM Main_CTE
 )
SELECT *
FROM Main_CTE
 ,Count_CTE
ORDER BY Main_CTE.SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY

Method 3

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount]
FROM [dbo].[SalesOrderDetail]
CROSS APPLY (
 SELECT COUNT(*) TotalCount
 FROM [dbo].[SalesOrderDetail]
 ) [Count]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY
GO