Firoz Ansari's Weblog

SQL Server 2005 - Data Paging

It now becomes a UI standard for displaying records in paginated grid format. If you have list of employees then you always wanted to only show 10 (or something) at a time. And if user wants to see next set of employees he should click on the next button which will pull next set of 10 employees. So this way, every time you will have controlled limit of payload which will pass from server to browser.

 

There is a common technique used by developers to use induced a primary key in my temporary table and show paginated records from there. You must wanted to learn that approach. Visit:

 

Manual Paging, part I

http://mceahern.manilasites.com/dotnet/pagingpart1

 

Paging: Use ADO, getrows, or a Stored Procedure?

http://www.15seconds.com/issue/010308.htm

 

Is Paging with Recordsets the Best Method?

http://www.15seconds.com/issue/010607.htm

 

But now in SQL Server 2005, you don’t need to create any temp table etc. to achieve the same result set. Microsoft extent SELECT statement with new ranking function ROW_NUMBER() which return row number in result set itself.

 

SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate

FROM Orders

 

Which will give result some thing like:

RowNumber            OrderID     OrderDate

-------------------- ----------- -----------------------

1                    10248       1996-07-04 00:00:00.000

2                    10249       1996-07-05 00:00:00.000

3                    10250       1996-07-08 00:00:00.000

4                    10251       1996-07-08 00:00:00.000

5                    10252       1996-07-09 00:00:00.000

6                    10253       1996-07-10 00:00:00.000

7                    10254       1996-07-11 00:00:00.000

8                    10255       1996-07-12 00:00:00.000

9                    10256       1996-07-15 00:00:00.000

10                   10257       1996-07-16 00:00:00.000

 

 

So now you can utilize this row number of the record to qualify the required records from whole table. How? Here I go

 

WITH Ordered AS (

SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate

FROM Orders)

SELECT *

FROM Ordered

WHERE RowNumber between 21 and 30

 

And result will be something like

RowNumber            OrderID     OrderDate

-------------------- ----------- -----------------------

21                   10268       1996-07-30 00:00:00.000

22                   10269       1996-07-31 00:00:00.000

23                   10270       1996-08-01 00:00:00.000

24                   10271       1996-08-01 00:00:00.000

25                   10272       1996-08-02 00:00:00.000

26                   10273       1996-08-05 00:00:00.000

27                   10274       1996-08-06 00:00:00.000

28                   10275       1996-08-07 00:00:00.000

29                   10276       1996-08-08 00:00:00.000

30                   10277       1996-08-09 00:00:00.000

I know this will going to save lot of afford for me.

Posted: Jun 12 2005, 06:09 AM by firoz.ansari | with 17 comment(s)

Comments

Andrey Skvortsov said:

Only IF "select" will return same dataset everytime-that's problem that we had in oracle;-)
# June 12, 2005 9:07 AM

My implementation said:

This is cool and its also going to replace my code as well.

Guy S.
# June 12, 2005 1:19 PM

Frans Bouma said:

" Only IF "select" will return same dataset everytime-that's problem that we had in oracle;-)"
:) That's unsolveable, you can only solve that by keeping the connection open AND storing the complete resultset in a temptable and page through it while the user processes the page requested... very inefficient. :)

I'm very happy SqlServer finally got a proper paging mechanism.
# June 13, 2005 4:08 AM

Firoz Ansari said:

I am also satisfied with this mechanism of paginating results. ROW_NUMBER() fully addressed the requirement to render restricted numbers of result on the page and that it without any kind of tweak.

I have also worked with MySQL, which have its own way of paginating results:

SELECT OrderID, OrderDate
FROM Orders
LIMIT 21, 30

Am I asking for more! :)

Firoz Ansari
# June 13, 2005 4:23 AM

Andres Aguiar said:

The problem with the Common Table Expression solution is that you cannot do an 'order by' on it, so it's pretty useless for paging, as if you don't have an order by then the order in which the rows can be retrieved is not deterministic...

This one works:

SELECT [OrderID]
,[ProductID]
,[UnitPrice]
,[Quantity]
,[Discount]
FROM (SELECT TOP 200
[OrderID], [ProductID] ,[UnitPrice] ,[Quantity] ,[Discount], ROW_NUMBER() OVER(ORDER BY [ProductID]) as row
FROM [Northwind].[dbo].[Order Details] Order by [Productid]) x
WHERE x.row BETWEEN 190 and 200


and the syntax is pretty similar to DB2's one.
# June 13, 2005 11:36 AM

Kthaker said:

i have column with comma separated i.e

Item

---

1,2,3,4,5,6

Now i wrote on function to split those values

so i got

Item

----

1

2

3

4

5

6

now i want to fetch data from this table with specified row id.

I mean i want data from the rows which user pass.

How do i achieve ??

Plz help me

# July 4, 2007 9:53 AM

Rajesh Kumar said:

Really it's cool & help full to me also

# July 10, 2007 1:34 AM

Deus said:

We are trying this kind of paging with 1 million of records, it works fine for the first pages, but if the user wants to go to the lastest pages we are getting timeout because the query is taking more than 2 seconds for finishing. This is happening because according to the script the SQL Server is scanning all the records from @StartRecord to @EndRecord.

I know this is an uncommon case, but we have to support it.

Do you have any idea to support this case? Does anybody else has proved this kind of paging with 1 millon of records?

# September 10, 2007 5:07 PM

Ramesh said:

I think we can use " select top "  keyword to minimize the risk of timing out issue  for  the above scenario.

# October 7, 2007 3:00 PM

Bernard said:

Thx a lot man... your article help me pagination in sql server 2005

# February 3, 2008 2:06 AM

AT said:

how to get the total number of rows .. say the actually result set has 500 rows..

I want to display the total number of rows to the user

# February 14, 2008 9:39 AM

firoz.ansari said:

AT, You have fire another select statement to get total number of rows. My suggestion will be, to keep both queries (pagination and total record) in single stored procedure instead of two database call.

Something like this:

CREATE PROCEDURE [dbo].[usp_GetPagedOrder]

@StartPage INT = 1

AS

BEGIN

WITH Ordered AS (

SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate

FROM Orders)

SELECT *

FROM Ordered

WHERE RowNumber between @StartPage and @StartPage+9

SELECT COUNT(*)

FROM Orders

END

# February 14, 2008 10:53 AM

Okey said:

very thanks

# April 21, 2008 5:26 PM

DragonGod said:

This is excellent.

Thank you.

# October 17, 2008 8:37 AM

Estetik said:

I'm guessing the only way to do this in Javascript would be to use the onresize event, and then using the resizeTo method to attempt to keep the window at the size you want?

# November 16, 2008 1:22 PM

Yahya said:

Faraz, this is a related but is from access. I have four sql that bring first, next, prev and last recs respectively as below, not sure how to implement in sql server using ROW_NUMBER(), if at all this is feasible. Wonder if you can give some pointers.

First

SELECT TOP 1 <field list>

FROM Clients

WHERE (Status = @Status)

ORDER BY CompanyID

Next

SELECT TOP 1 <field list>

FROM Clients

WHERE (CompanyID > ?) AND (Status = @Status)

ORDER BY CompanyID

Prev

SELECT TOP 1 <field list>

FROM Clients

WHERE (CompanyID < ?) AND (Status = @Status)

ORDER BY CompanyID DESC

Last

SELECT TOP 1 <field list>

FROM Clients

WHERE (Status = @Status)

ORDER BY CompanyID DESC

# June 3, 2009 1:52 PM

firoz.ansari said:

I am not sure but you don't need four separate queries for First/Prev/Next/Last link. Only one SQL statement will fetch data based on which "page" you want to show. Modified SQL will be look like this:

WITH ClientList AS (

SELECT ROW_NUMBER() OVER (ORDER BY CompanyID) AS RowNumber, <field list>

FROM Clients

)

SELECT *

FROM ClientList

WHERE RowNumber between 1 and 10

Put above SQL in SP and pass two additional arguments, PageIndex & PageSize which used to determine lower and upper record number.

In SP:

SET @PageLowerBound = @PageSize * @PageIndex

SET @PageUpperBound = @PageLowerBound + @PageSize

WITH ClientList AS (

SELECT ROW_NUMBER() OVER (ORDER BY CompanyID) AS RowNumber, <field list>

FROM Clients

)

SELECT *

FROM ClientList

WHERE RowNumber between @PageLowerBound and @PageUpperBound

Page will hold the current page number. If you click on Next, add +1 to the current page and pass it to this SP. Same way, if you click on Prev, substract 1 from current page.

Hope this help.

# June 3, 2009 2:46 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)