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 page in large result set?

Execute the following T-SQL scripts in Query Analyzer or Management Studio Query Editor to demonstrate paging a large result sets. The inner most query (CTE, select from select, subselect, derived table x) can be replaced with desired query.


-- SQL paged results - paging results using T-SQL - sql paging row_number
-- SQL applying ROW_NUMBER and CTE for paging results - sql set rowcount

DECLARE @LinesPerPage int,
@PageNumber int,
@StartRow int
SET @LinesPerPage = 40
SET @PageNumber =10
SET @StartRow = @PageNumber * @LinesPerPage
 
;WITH CTE AS
(SELECT I.CustomerID,
        C.FirstName,
        C.LastName,
        A.AddressLine1,
        A.City,
        SP.Name                                        AS State,
        CR.Name                                        AS Country,
        ROW_NUMBER()
          OVER(ORDER BY SP.Name, A.City, I.CustomerID) AS RN
FROM   AdventureWorks.Person.Contact AS C
       INNER JOIN AdventureWorks.Sales.Individual AS I
         ON C.ContactID = I.ContactID
       INNER JOIN AdventureWorks.Sales.CustomerAddress AS CA
         ON CA.CustomerID = I.CustomerID
       INNER JOIN AdventureWorks.Person.Address AS A
         ON A.AddressID = CA.AddressID
       INNER JOIN AdventureWorks.Person.StateProvince SP
         ON SP.StateProvinceID = A.StateProvinceID
       INNER JOIN Person.CountryRegion CR
         ON CR.CountryRegionCode = SP.CountryRegionCode )
 
SELECT TOP (@LinesPerPage) * FROM CTE
WHERE RN >= @StartRow
ORDER by RN

-

CustomerID FirstName LastName AddressLine1 City State Country RN
21347 Alisha Raji 912 Fremont St. Cliffside British Columbia Canada 400
21431 Preston Kapoor 3788 Gordon Ct. Cliffside British Columbia Canada 401
21453 Wayne Deng 7495 Morgan Territory Rd. Cliffside British Columbia Canada 402
21480 Alfredo Martin 9896 White Dr Cliffside British Columbia Canada 403
21489 Neil Gutierrez 7225 Newell Ave. Cliffside British Columbia Canada 404
21577 Tiffany Ma 5444 Bellord Ct. Cliffside British Columbia Canada 405
21582 Amber Roberts 9665 Geneva Ave. Cliffside British Columbia Canada 406
21614 William Thomas 1277 Live Oak Avenue Cliffside British Columbia Canada 407
21631 Martin Sanchez 1267 Baltic Sea Ct. Cliffside British Columbia Canada 408
21690 Samantha Thomas 2982 Evelyn Court Cliffside British Columbia Canada 409
21803 Emily Thomas 1172 Flamingo Dr. Cliffside British Columbia Canada 410
21925 Tabitha Dominguez 8108 Abbey Court Cliffside British Columbia Canada 411
22008 Alisha Ye 9525 Canyon Way Cliffside British Columbia Canada 412
22026 Bridget Kennedy 3376 Jacaranda Dr. Cliffside British Columbia Canada 413
22027 Franklin Luo 5763 Reed Way Cliffside British Columbia Canada 414
22058 Jake Zukowski 5888 Salem St. Cliffside British Columbia Canada 415
22114 Lucas Martin 191 Trail Way Cliffside British Columbia Canada 416
22143 Summer Prasad 2328 Elk Dr Cliffside British Columbia Canada 417
22264 Stephanie Coleman 7182 Olive Hill Cliffside British Columbia Canada 418
22285 Meredith Moreno 6832 Le Jean Way Cliffside British Columbia Canada 419
22713 Joseph Miller 3570 Court Lane Cliffside British Columbia Canada 420
22728 Arianna Russell 5537 Virginia Hills Cliffside British Columbia Canada 421
22755 Christian Brown 3106 Julpum Loop Cliffside British Columbia Canada 422
22877 Xavier Griffin 6936 Woodruff Lane Cliffside British Columbia Canada 423
22893 Chloe Miller 7390 Discovery Bay Cliffside British Columbia Canada 424
22971 Morgan Howard 6854 Veale Ave. Cliffside British Columbia Canada 425
23009 Dalton Thomas 1431 Rancho View Drive Cliffside British Columbia Canada 426
23267 Juan James 9221 Dutch Slough Rd. Cliffside British Columbia Canada 427
23290 Angelica Bennett 5578 Burning Barn Dr Cliffside British Columbia Canada 428
23518 Katherine Adams 7660 Willow Creek Ct. Cliffside British Columbia Canada 429
23866 Xavier Flores 60 Reliz Valley Road Cliffside British Columbia Canada 430
24255 Chloe Lee 9342 Temple Drive Cliffside British Columbia Canada 431
24344 Wayne Chander 8989 Adelia Court Cliffside British Columbia Canada 432
24392 Molly Prasad 1588 Prestwick Drive Cliffside British Columbia Canada 433
24621 Chad Shan 6643 Mt. Whitney Cliffside British Columbia Canada 434
24625 Yolanda Nath 2467 Green St. Cliffside British Columbia Canada 435
24663 Ebony Fernandez 6228 Palm Avenue Cliffside British Columbia Canada 436
24744 Brendan Yuan 3949 Eastgate Ave. Cliffside British Columbia Canada 437
24805 Donna Luo 6312 Woodcrest Dr Cliffside British Columbia Canada 438
24960 Brianna Gray 1107 La Corte Bonita Cliffside British Columbia Canada 439

-----------

-- SQL paging - Paging recordset - Paging large datasets -- Paging T-SQL

USE Northwind;

 

DECLARE @LinesPerPage int,

            @PageNumber int,

            @StartRow int

SET @LinesPerPage = 40

SET @PageNumber =10

SET @StartRow = @PageNumber * @LinesPerPage

 

SELECT * FROM

      (SELECT TOP (@LinesPerPage) * FROM

            (SELECT TOP (@StartRow) *

            FROM [Order Details]

            WHERE UnitPrice > $10.0

            ORDER BY 1 ) x

      ORDER by 1 DESC) y

ORDER by 1
GO

-- RESULTS

OrderID ProductID UnitPrice Quantity Discount
10427 14 18.6 35 0
10429 50 13 40 0
10429 63 35.1 35 0.25
10430 17 31.2 45 0.2
10430 56 30.4 30 0
10430 59 44 70 0.2
10431 17 31.2 50 0.25
10431 40 14.7 50 0.25
10432 26 24.9 10 0
10433 56 30.4 28 0
10434 11 16.8 6 0
10434 76 14.4 18 0.15
10435 2 15.2 10 0
10435 22 16.8 12 0
10435 72 27.8 10 0
10436 56 30.4 40 0.1
10436 64 26.6 30 0.1
10437 53 26.2 15 0
10438 34 11.2 20 0.2
10438 57 15.6 15 0.2
10439 12 30.4 15 0
10439 16 13.9 16 0
10439 64 26.6 6 0
10440 2 15.2 45 0.15
10440 16 13.9 49 0.15
10440 29 99 24 0.15
10440 61 22.8 90 0.15
10441 27 35.1 50 0
10442 11 16.8 30 0
10442 66 13.6 60 0
10443 11 16.8 6 0.2
10443 28 36.4 12 0
10444 17 31.2 10 0
10444 26 24.9 15 0
10444 35 14.4 8 0
10445 39 14.4 6 0
10447 65 16.8 35 0
10447 71 17.2 2 0
10448 26 24.9 6 0
10448 40 14.7 20 0

-----------

Related link on Paging Results:

http://www.sqlusa.com/bestpractices2005/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