SQL Server 2005 - Data Paging

re: SQL Server 2005 - Data Paging

firoz.ansari — Wed, 03 Jun 2009 18:46:38 GMT

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.

re: SQL Server 2005 - Data Paging

Yahya — Wed, 03 Jun 2009 17:52:21 GMT

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

SELECT TOP 1 <field list>

FROM Clients

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

ORDER BY CompanyID

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

re: SQL Server 2005 - Data Paging

Estetik — Sun, 16 Nov 2008 18:22:09 GMT

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?

re: SQL Server 2005 - Data Paging

DragonGod — Fri, 17 Oct 2008 12:37:31 GMT

This is excellent.

Thank you.

re: SQL Server 2005 - Data Paging

Okey — Mon, 21 Apr 2008 21:26:25 GMT

very thanks

re: SQL Server 2005 - Data Paging

firoz.ansari — Thu, 14 Feb 2008 15:53:27 GMT

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

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

re: SQL Server 2005 - Data Paging

AT — Thu, 14 Feb 2008 14:39:18 GMT

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

re: SQL Server 2005 - Data Paging

Bernard — Sun, 03 Feb 2008 07:06:37 GMT

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

re: SQL Server 2005 - Data Paging

Ramesh — Sun, 07 Oct 2007 19:00:37 GMT

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

re: SQL Server 2005 - Data Paging

Deus — Mon, 10 Sep 2007 21:07:06 GMT

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?