CREATE PROCEDURE [dbo].[paging_example]
  @slice_size int = 0, -- ROWCOUNT 0 will return all results
  @slice_start int = 1
AS

declare @slice_end int
set @slice_end   = @slice_start + @slice_size - 1

-- Create a buffer for the records from 1..@slice_end
declare @paging_buffer table(
  sort_id int not null identity(1,1) PRIMARY KEY,
  user_id int not null
);


-- SELECT from 1 til the end of the slice into @paging_buffer
SET ROWCOUNT @slice_end
insert into @paging_buffer (user_id)
SELECT 
  user_id
FROM Users
ORDER BY lastname

SET ROWCOUNT 0
select u.user_id, u.firstname, u.lastname
from @paging_buffer buffer
inner join Users u on u.user_id = buffer.user_id
where buffer.sort_id >= @slice_start 
and buffer.sort_id <= @slice_end
order by buffer.sort_id

GO

##</code><code>##

CREATE PROCEDURE [dbo].[paging_example]
  @slice_size int = 0, -- ROWCOUNT 0 will return all results
  @slice_start int = 1
AS

-- Create a buffer for the records from 1..@slice_end
declare @paging_buffer table(
  user_id int
);


declare @slice_end int
set @slice_end   = @slice_start + @slice_size - 1

-- SELECT from 1 til the end of the slice into @paging_buffer

SET ROWCOUNT @slice_end
insert into @paging_buffer
SELECT 
  user_id
FROM Users
ORDER BY lastname

--This approach will delete the rows we don't need from the buffer
-- If we're starting at #1 then we don't need to do anything special
-- Delete will go in order, so if we delete up to the record before the start of the slice then only the desired records will remain

DECLARE @discard_up_to int
SET @discard_up_to = @slice_start - 1
SET ROWCOUNT @discard_up_to

-- If the start_slice is 1, then setting the rowcount to 0 will result in a complete delete - we don't want this
-- But we also do not want to prevent if with an IF clause, because when a stored procedure uses the 'IF' clause it will be recompiled before each run.
-- So instead we check "where @discard_up_to > 0" in the delete clause

DELETE FROM @paging_buffer where @discard_up_to > 0

SET ROWCOUNT 0
select u.user_id, u.firstname, u.lastname
from @paging_buffer buffer
inner join Users u on u.user_id = buffer.user_id
option (loop join, force order)

GO