Q
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.

This was last published in February 2005

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close