Hmnt avatar image
Hmnt asked

Consolidated: SQL 2005+ Dynamic Pagination using SET ROWCOUNT or RowNumber() ?

I've a complex SP which applies multiple JOINs and lookup and come complex filters like comma-separated values, etc... On top of it, I've to deploy two complex yet performance-effective features:

1. Dynamic sorting but I see its limited - you kno the long/clumsy CASE hierarchy, its strange that experts also agree that this is the only 'best' solution we've got:

Anyway, I don't expect much on this one for now.

2. Dynamic pagination - that is I want the SP to be able to return only X number of records (X = page size) starting from Y (Y = page number). I hope you've got the general idea.

To make it more clear I want to use something available in MySQL & PostgreSQL:

[LIMIT { number | ALL }] [OFFSET number]

Its strange such a simple & basic functionality is NOT available in SQL 2005+ .. or am I wrong (I'd be glad to hear it :-))

I've known two approaches which suite my performance\complexity tradeoff -

[2.1] Using the 'RowNumber()' feature of SQL 2005 and then applying filter: (I've used it in past)

WHERE (Row BETWEEN (@PageIndex-1) * @PageSize +1 AND @PageIndex* @PageSize)                    

But again, this needs creating a temp table or using a WITH clause. This is also explained in:

[2.2] I found some new ways. One of them is using the -


And they say that overall 2.2 is effective then 2.1. Is it? Also, I wanted to know what happens if two user-requests trigger the same SP twice simultaneously .. I hope the 'SET ROWCOUNT' won't be 'shared' or 'over-written' among simultaneous SP-calls. Pls confirm. Any other points comparing 2.1 & 2.2 ?

Pheew .. I hope I've done my homework in this consolidated analysis I've shared. Pls rate it and share your thots. Let me know if this has been useful.

Thank you.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

TG avatar image
TG answered

Paging is one of those things where there may not be a single "best method" for all circumstances. Efficient paging is a big topic and, similar to query optimization, can be somewhat of an art to perfect.

A dynamic sql solution may work well for a very flexible search SP with lots of optional parameters where the user's criteria will affect which tables are included in the FROM clause as well as dictate if a GROUP BY clause is necessary.

If the data is stored in a "pre-compiled" reporting server it may make sense to pre-rank the data by all possible sorting options. Obviously that is not possible in an OLTP system.

Sometimes a total number of rows is required (so you can show "page 2 of 102"). That requirement can throw a wrench into some cool solutions - like the nested TOP techniques.

The bottom line is that you need to compare ALL your solutions for execution times, number of reads and exectution plans for a reasonable range of possible calls. It is also important to look at those factors as well as server metrix under a concurrency load. Because what may work great for one call may crash and burn when 50 people are running it concurrently.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.