Advanced Paging / Pagination Techniques in SQL

Transact-SQL
1

I've been revisiting my Paging code (display 20 rows with a NEXT PAGE link and perhaps a list of Page Numbers). Compared to what we used to do in SQL 2000 the current methods are much more efficient.

I've read many articles on this, but a lot of them are not "real world". "Real world" for me is a query on multiple, joined, tables with a WHERE clause, many of the examples "cheat" by assuming that the total number of rows can be got from SQL's Meta Data for the table (no WHERE clause??!) and so on

This type of simple paging query:

SELECT ProductCode, ProductName
FROM   MyProducts
ORDER BY ProductCode
OFFSET  @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

just doesn't happen in the real world! My Products are displayed for a given Category with out-of-stock products sorted last and IsObsolete ones ignored.

What I can assume is that I have a perfect index available for the job.

One pet hate I have is to press NEXT PAGE and see one/some/many rows repeated. This is because some new rows have been added to (what was) Page 1. So I prefer the SEEK type of paging where I display the next page AFTER the last row on the previous page. That gives me a bit of bother with the whole Page Number thing.

I'd appreciate any ideas anyone has please.

My thought is:

Display the next, say, 20 rows (i.e. "A page") greater than the Last Row displayed (or the previous 20 rows less than the First Row if the user pressed Previous Page)

I can do that easily.

However, the way my code works means that I need to provide (in a separate resultset) the First & Last IDs of the rows on the page (the Client side won't work that out from the provided data) and also the Row Count, or Current Page Number, or similar (if I am to provide a list of Page No.s)

To display Page Numbers I need to know what page I am on (approx might be good enough) so that I can offer the user to go back to specific Page Numbers earlier in the list. Perhaps I can just do a COUNT(*) of how many rows are lower than my current position? (and divide by Page Size to convert it to Page Number) but that sounds to me like a costly CPU operation??

Or perhaps I should dispense with Page Number altogether and offer a +1 Page (Next/Previous) and +10 Pages (Fast Forward/Back) option instead?

My assumption (what do you think?) is that users page one-by-one and don't page very far - who's going to sit there and page through 100 pages? When I find a shopping site with more than 10 pages of products displayed and no Filter / Sub-Category to refine the list, I find a different shop!!

I am doubtful that a user will choose Page 10 (unless they are guessing, on a site with LOTS of pages available) approximately where "My Item" is going to be. In effect performing something akin to a Binary Chop Search.

So perhaps Page Numbers is not that useful anyway, and +1 & +10 would do?

IF I had page numbers then I would like to show the actual first ID number of the record on that page - so the user can see, from a drop down list of pages, what / where abouts in the Alphabet that page would take them to.

Page 1 01-Jan-2015
Page 2 14-Feb-2015
Page 3 23-Mar-2015
...

the the user would not have to guess for the Binary Chop Search, then could get very close to it in one step. But to do that I'm going to have to pull all the rows to work out what the ID is for each page - or is there a smarter way?

I've seen sites list A, B, C, ... Z links which is fine for products displayed alphabetically. Perhaps I could just display a "Jump to" field based on the main key field of the Page Ordering - and then display the first page where ThatColumn >= @UsersInputValue ??

Please let me know which of these ideas you think would work / you like or hate :slight_smile: or if you have seen other ideas that you think work well

In case of interest here's my paging code (this version for SQL2008 and earlier, see the comments for the SQL2012 method

;WITH ctePage AS 
(
	SELECT
/**SQL <=2008 */
		ROW_NUMBER() OVER
		(
			ORDER BY SortCol1, SortCol2, UniqKey1, UniqKey2
		) AS RowNumber,
/* <=2008 **/
--				
		UniqKey1, UniqKey2	-- Unique column(s), preferably the Clustered Index columns
	FROM	dbo.MyTable
	WHERE	    IndexedCol1 >= @Col1START
		AND IndexedCol1 < @Col1END
		AND TieBreakColumn > @TieBreakColumn
--
/**SQL >=2012 *
	ORDER BY SortCol1, SortCol2, UniqKey1, UniqKey2
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY
* SQL >=2012 **/
)
SELECT
	T.Col1,
	T.Col2,
	...
FROM	ctePage AS CTE
-- NOTE: From my tests EXISTS is twice as slow as a JOIN here
	JOIN dbo.MyTable AS T
		 ON T.UniqKey1 = CTE.UniqKey1
		AND T.UniqKey2 = CTE.UniqKey2
/**SQL <=2008 */
WHERE	RowNumber BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
/* <=2008 **/
ORDER BY RowNumber