DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to return the nth page of a result set?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE