Database side pagination

Pagination is a process that is used to divide a large volue data into smaller data to display on the screen this process is also known as paging.
This is commonly used by applications and can be seen on 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