CREATE PROCEDURE [dbo].[_Paging]
@PageNumber int = 3, 
@PageSize int = 2
as
declare @Table as table (Id int primary key)
insert @Table (Id) values(1) insert @Table (Id) values(2) insert @Table (Id) values(3) insert @Table (Id) values(4) insert @Table (Id) values(5)
declare @Offset int
set @Offset = (@PageNumber - 1) * @PageSize + 1
--1. TOP (N) (нужно считать @ActualPageSize)
select *
	from
	(select top (@PageSize)	* 
		from (select top (@PageSize * @PageNumber) *
				from @Table
				order by Id desc) as T1
		order by Id asc) as T2
	order by Id desc
--2. ROW_NUMBER
select * 
	from
	(select *, row_number() over (order by Id desc) as row_number 
		from @Table) T
	where row_number between @Offset and @Offset + @PageSize - 1;
--3. ROW_NUMBER with CTE
with T as 
(select *, row_number() over (order by Id desc) as row_number
		from @Table)
select * 
	from T
	where row_number between @Offset and @Offset + @PageSize - 1;
--4. ROW_NUMBER with CTE and JOIN
with T as 
(select Id, row_number() over (order by Id desc) as row_number
		from @Table)
select T1.* 
	from T join @Table T1 on T.Id = T1.Id
	where row_number between @Offset and @Offset + @PageSize - 1;
--5. SET ROWCOUNT
declare @OffsetId int
set rowcount @Offset
select @OffsetId = Id 
	from @Table
	order by Id desc
set rowcount @PageSize
select *
	from @Table
	where Id <= @OffsetId
	order by Id desc