Although there is already some infrastructure for paging in many development tools, sometimes a custom solution is needed.
Here's a T-SQL example of how to create your own paging data within your query. In this example, I have aggregated four additional columns. Replace 'sys.sysobjects' with the table that you wish to query. Replace 'sys.sysobjects.[name]' with the column list that you would like to use to order the query results. The @pagesize variable can be modified to set the page size.
declare @resultcount int
set @resultcount = (select count(*) from sys.sysobjects)
declare @pagesize int
set @pagesize = 10
SELECT
row_number() over (order by sys.sysobjects.[name]) as 'ResultCount',
convert(int, (row_number() over (order by sys.sysobjects.[name]) - 1) / @pagesize) + 1 as 'CurrentPage',
convert(int, @resultcount / @pagesize + 1) as 'MaxPages',
@resultcount as 'MaxResult',
sys.sysobjects.*
FROM
sys.sysobjects