Problem solve Get help with specific problems with your technologies, process and projects.

Paging through SQL query results

What is the best way to do pagination on SQL query results? I used LIMIT M,N in MySQL. Is there something like that in SQL?

The best way is to query only those rows that you need, and send only those rows back from the database to the front end.

MySQL's LIMIT M,N actually assembles the entire result set (imagine if there are a million rows!), but sends only N rows back. This less than optimal.

When you ask "Is there something like that in SQL?" you are presumably asking about Microsoft SQL Server, and the answer is no, it's even messier in SQL Server. Your first attempt to reproduce the M,N functionality might involve nesting a derived table, something like this:

 select top N col1, col2, sortcol from ( select top M+N col1, col2, sortcol from yourtable order by sortcol desc ) as derivedtable order by sortcol asc

However, as you near the end of the table, you can see that this is quite inefficient.

Another solution involves keeping track of the value of the sort column from the last time the query was run, so that you don't need a derived table:

 select top N col1, col2, sortcol from yourtable where sortcol > savedvalue order by sortcol asc

Here, savedvalue is the value of the last sortcol from the previous execution. Of course, you need to modify the query on the first execution.

Database pagination is not a simple problem, and will have many types of solution. When evaluating strategies, remember that you want to:

  1. minimize query complexity and execution time

  2. minimize the number of rows transferred back to the front end

These objectives often conflict with each other.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.