T-SQL Paging Sample

Table of Contents

• Introduction
• T-SQL Paging Example
• Conclusion

Introduction

This is an older approach to pagination in T-SQL. There are better ways to do it now (such as OFFSET/FETCH), but this is a sample example of how to do pagination at the SQL level using ROW_NUMBER.

T-SQL Paging Example

declare @requestMaxReturnItems int
declare @requestPageNumber int
declare @startRow int
declare @endRow int
declare @totalPageByMaxReturnNumber int
declare @totalCount int;

-- temp test

-- request from ui
-- max items return from this sql
set @requestMaxReturnItems = 10;
-- which page number to return
set @requestPageNumber = 1
--set @startRow = 1
--set @endRow = 10

select @totalCount = count(*) FROM [OrangeGrid].[dbo].[DiagnosticLog];
set @totalPageByMaxReturnNumber = @totalCount / @requestMaxReturnItems;
set @startRow = (@requestPageNumber - 1) * @requestMaxReturnItems;
set @endRow = @requestPageNumber * @requestMaxReturnItems;

print @totalPageByMaxReturnNumber

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY YourTable.CreateDate desc ) AS RowNum, *
          FROM      YourTable
        ) AS t
WHERE   RowNum > @startRow
    AND RowNum <= @endRow
ORDER BY RowNum

Conclusion

This T-SQL paging approach uses ROW_NUMBER() to assign row numbers and then filters by a calculated range based on the page number and page size. For SQL Server 2012 and later, consider using OFFSET/FETCH NEXT for a cleaner pagination syntax.