Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor to demonstrate paging in query results. Typical use: paging through information on a web page.

-- SQL page results - SQL page recordset - SQL page records

USE tempdb

GO

SET nocount  ON

-- SQL create table

CREATE TABLE PayRegister (

  PayRegisterID INT    IDENTITY    PRIMARY KEY, -- key

  EmployeeID    INT,                            -- key (FK)

  Pay           MONEY,                          -- data

  PayCheckDate  SMALLDATETIME,                  -- data

  ModifiedDate  DATETIME default (getdate()))   -- row maintenance

GO

-- SQL insert into table

-- SQL insert values

-- SQL populate table

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (525, 1157.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (125, 13257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (225, 14257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (325, 15257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (425, 17257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 1257.13,'1/2/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (545, 7357.18,'1/9/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (855, 7357.18,'1/10/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 9257.30,'1/21/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (551, 6257.30,'1/21/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 1257.15,'1/25/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 1057.99,'2/4/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (553, 3457.23,'2/7/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 3457.87,'2/12/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 5857.34,'2/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (955, 13457.23,'2/25/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (155, 9857.26,'3/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4557.32,'3/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (355, 4757.30,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (455, 3557.25,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (655, 15757.23,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (575, 2457.56,'3/31/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 3457.23,'4/4/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 2357.99,'4/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (350, 5657.35,'4/8/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (455, 14757.53,'6/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (575, 3057.56,'7/7/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (535, 6557.34,'7/8/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4457.55,'8/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 18457.25,'8/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 2357.56,'8/22/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4557.77,'9/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (545, 6557.95,'9/26/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (525, 16557.95,'9/26/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (544, 26557.95,'9/26/2017')

GO

-- SQL page query results

DECLARE  @PageSize INT,

         @PageNo   INT

SET @PageSize = 5

SET @PageNo = 3

-- SQL row_number over order by - row number, row id

-- SQL between operator

SELECT PayRegisterID,

         EmployeeID,

-- SQL currency formatting

         '$'+convert(varchar,Pay,1) AS Pay,

       PaycheckDate,

         RowNumber

FROM   (SELECT *,

               Row_number()

                 OVER(ORDER BY PayRegisterID) AS RowNumber

        FROM   PayRegister) AS a

WHERE  RowNumber BETWEEN (@PageNo - 1) * @PageSize + 1 AND (@PageNo * @PageSize)

SET nocount  OFF

GO

/* Results

PayRegisterID     EmployeeID  Pay         PaycheckDate            RowNumber

11                595         $1,257.15   2017-01-25 00:00:00     11

12                255         $1,057.99   2017-02-04 00:00:00     12

13                553         $3,457.23   2017-02-07 00:00:00     13

14                585         $3,457.87   2017-02-12 00:00:00     14

15                555         $5,857.34   2017-02-15 00:00:00     15

*/

-- Cleanup

drop table tempdb.dbo.PayRegister