Anatoly Lubarsky Logo
programming, design, integration, games, music

T-SQL: Paging with ROW_NUMBER()

In MSSQL 2000 we used to do paging either by dynamic sql or by some advanced techniques like the example with rowcount. In MSSQL 2005 with the introduction of ROW_NUMBER function life is a lot easier.


DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

Related Posts:

Friday, November 18, 2005 11:59 PM

Comments

# Hey Man
Can I hire you? Where do you live?

Josh
josh@airlevel1.com

11/30/2005 6:24 PM by Josh

# re: T-SQL: Paging with ROW_NUMBER()
Josh: I live in Israel. You can hire me for a big money :)
May be ...

11/30/2005 8:26 PM by Anatoly Lubarsky

# re: T-SQL: Paging with ROW_NUMBER()
For the order by which you use for the last sql

ORDER BY OrderDate
,OrderID;

you should use order by rownum instead.

2/1/2006 5:00 AM by Mohd Taufik

# re: T-SQL: Paging with ROW_NUMBER()
Mohd:

Why ? What the difference ?

2/4/2006 4:41 AM by Anatoly Lubarsky

# MSSQL 2005: Paging with ROW_NUMBER is the fastest

3/24/2006 11:23 PM by Anatoly Lubarsky

# re: T-SQL: Paging with ROW_NUMBER()
i want to develop an web service using C#.i am going to fetch large amount of data from server which is in some other country.is there any way to compress the data?
i am using SQL 2000.

11/12/2008 7:18 AM by porchelvi

# T-SQL For Paging « Ashfaq's programming Blog
T-SQL For Paging « Ashfaq's programming Blog

5/11/2010 1:01 AM by Pingback/TrackBack

# SQL resultaat uit database opsplitsen in pagina's - 9lives - Games Forum
SQL resultaat uit database opsplitsen in pagina's - 9lives - Games Forum

12/12/2011 6:08 AM by Pingback/TrackBack

# T-SQL: Paging with ROW_NUMBER() | mytechconnect
T-SQL: Paging with ROW_NUMBER() | mytechconnect

3/22/2013 5:36 AM by Pingback/TrackBack

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs