Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Server Side Paging With SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, August 09, 2007 12:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3181.asp

.
Post #389422
Posted Friday, August 24, 2007 10:15 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi all,

again, the source code listing does not show up well in IE. The lines are wrapped at places where they are not supposed to be.

The code shows up correctly in Mozilla.

If you are using IE, i would suggst to click on the link 'code' and read the source code from there.

 



.
Post #393843
Posted Tuesday, August 28, 2007 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 15, 2011 10:05 AM
Points: 1, Visits: 8
Jacob,

I've read seemingly a bazillion SQL Server pagination articles/tutorials, and I must say, I think that yours is head and shoulders above the rest. Very clearly worded, and more specifically, extremely well commented code. Thank you!

Post #394566
Posted Wednesday, August 29, 2007 12:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 2:11 PM
Points: 1,588, Visits: 384
Good article, although every server based pagination sample I read seems to skip the fact that in almost every situation the "user" wants to know how many records there are in total, including any specified filters. This allows any client side paging mechanism to say "you're on page 3 of 15" etc.

Is there any way to do this without using a temp table/variable as this seems to be a much less elegant solution than the ROW_NUMBER paging shown here ?




Dan
www.firstcs.co.uk
Post #394577
Posted Wednesday, August 29, 2007 12:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi noggin,

I had found 3 points that I must add to the above article after playing with a few real-life assignments.

  1. Find the total number of records affected by the current filters
  2. Support for sorting ascending and descending (usually, as a result of clicking on the header of a grid/table at the front end)
  3. Some ASP.NET code which shows how this code would work with an ASP.NET grid control (includes how to display custom page numbers at the front end  etc..)

I have a follow-up article coming up, which shows some sample code which may be helpful to clear those 3 points.

thanks

Jacob



.
Post #394578
Posted Wednesday, August 29, 2007 2:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 05, 2014 4:04 AM
Points: 44, Visits: 52

Jacob,

I read your article what I found very useful.
Now I have a question related to joins. Please consider this two examples:


WITH EmployeesOrders AS (
 SELECT 
   Employees.EmployeeID,
   Employees.FirstName,
   Employees.LastName,
   Orders.OrderDate,
   Orders.ShippedDate,
   ROW_NUMBER()OVER (ORDER BY LastName) AS RecNum
 FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)

SELECT * FROM EmployeesOrders
WHERE RecNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
ORDER BY RecNum

and

WITH EmployeesOrders AS (
 SELECT 
   Employees.EmployeeID,
   ROW_NUMBER()OVER (ORDER BY LastName) AS RecNum
 FROM Employees)

SELECT
  Employees.EmployeeID,
  Employees.FirstName,
  Employees.LastName,
  Orders.OrderDate,
  Orders.ShippedDate

FROM EmployeesOrders
  INNER JOIN Employees ON EmployeesOrders.EmployeeID=Employees.EmployeeID
  INNER JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE RecNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
ORDER BY RecNum


In the first query all the join and select stuff is done in the CTE (joining maybe 100000 records from a foreign table)
but in the second query the CTE returns only the ID needed to perform the selecting and the joining.
In this case the joins will have to deal only with the number of records given from the @Pagesize (maybe 15 or 20) and I think that this would be much more efficient in a 100000 records scenario.

Having to implement this in my current project which option should I choose ?

Many thanks

Franco Pizzinini

Post #394601
Posted Wednesday, August 29, 2007 2:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 11, 2008 2:10 AM
Points: 167, Visits: 2
I found this a really useful article since I hadn't played much with CTEs or ROW_NUMBER() before. Both of those concepts have suddenly 'clicked' and I really see the benefits of your approach. Just a few comments though, and apologies for being a bit picky:

Firstly, when you're applying the LastName, Title and City filters your LIKE clause starts with a wildcard - that's probably fine for small tables, but in large tables that's going to cause a table scan which will kill performance. If that's the application requirement then so be it, but for an example like yours I think you'd be better off removing the leading % so as not to encourage this practice. The whole idea of paging through result sets is to enable efficient IO and network bandwidth usage, particularly over large tables.

Secondly, several paging solutions I have seen do not scale at all well as the number or records in a table increases. Do you have any idea how well SQL Server optimizes the ROW_NUMBER function and how well your solution scales over big tables? I'll do a little investigation of my own when I get a chance, I've got some tables with several million records in which I'd be interested in doing some tests on.
Post #394607
Posted Wednesday, August 29, 2007 4:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 1,052, Visits: 862

Hi

I quite like the exposition of the rownumber and over stuff.  Interesting.

What I would ask is whether the performance issues relating to the and(x is null or x = y) that were experienced in SQL2000 (ie knocking it down to a scan) have been resolved on 2005?

 

Cheers




Post #394630
Posted Wednesday, August 29, 2007 4:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 2:11 PM
Points: 1,588, Visits: 384
Cheers Jacob, I look forward to the follow-up.



Dan
www.firstcs.co.uk
Post #394638
Posted Wednesday, August 29, 2007 4:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:57 AM
Points: 725, Visits: 1,149
I think that you can limit the initial rows you get in the CTE if you order the query and use the TOP clause

;WITH emp AS (SELECT TOP (@PageNumber * @PageSize)
/* retrieve only a limited result set
if you need records 31 -> 60, you retrieve in the first step only 60 rows
*/
CASE
WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)
WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)
WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City)
-- In all other cases, assume that @SortOrder = 'LastName'
ELSE ROW_NUMBER()OVER (ORDER BY LastName)
END AS RecID,
LastName,
FirstName,
Title,
HireDate,
City,
Country,
PostalCode
FROM employees
WHERE
(@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
AND
(@Title IS NULL OR Title LIKE '%' + @Title + '%')
AND
(@City IS NULL OR City LIKE '%' + @City + '%')

/*specify order */
ORDER BY
CASE
WHEN @SortOrder = 'Title' THEN Title
WHEN @SortOrder = 'HireDate' THEN HireDate
WHEN @SortOrder = 'City' THEN City
ELSE LastName
END
)
SELECT
RecID, -- This column is only for debugging/testing purpose.
LastName,
Title,
HireDate,
City
FROM emp
/*
you need only the last @PageSize rows from your result set
*/
WHERE RecID > (@PageNumber - 1) * @PageSize)
ORDER BY RecID


Post #394641
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse