Paging SQL Results

paging.sql

BEGIN TRANSACTION

CREATE TABLE x
(
    Id INT NOT NULL PRIMARY KEY
  , Name VARCHAR(150) NOT NULL
);


WITH generator (Id, Name) AS
(
  SELECT 1, NEWID()
  UNION ALL
  SELECT ID + 1, NEWID()
  FROM generator 
  WHERE ID < 20
)
INSERT INTO x (Id, Name)
SELECT Id, Name
FROM generator
OPTION (MAXRECURSION 0);

DECLARE @pageSize INT = 5;
DECLARE @currentPage INT = 0;
DECLARE @pageCount INT = (SELECT COUNT(*) / @pageSize FROM x);

WHILE (@currentPage < @pageCount)
BEGIN

  SELECT @currentPage + 1 AS Page, x.*
  FROM x
  ORDER BY x.Id
  OFFSET @pageSize * @currentPage ROW
  FETCH NEXT @pageSize ROWS ONLY

  SET @currentPage = @currentPage + 1;

END

ROLLBACK TRANSACTION