[ACCEPTED]-How to return a page of results from SQL?-pagination

On MS SQL Server 2005 and above, ROW_NUMBER() seems 1 to work:

T-SQL: Paging with ROW_NUMBER()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

I'd recommend either using LINQ, or try 12 to copy what it does. I've got an app where 11 I use the LINQ Take and Skip methods to 10 retrieve paged data. The code looks something 9 like this:

MyDataContext db = new MyDataContext();
var results = db.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

Running SQL Server Profiler reveals 8 that LINQ is converting this query into 7 SQL similar to:

SELECT [ProductId], [Name], [Cost], and so on...
FROM (
    SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
    FROM (
       SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER], 
           [ProductId], [Name], [Cost]
       FROM [Products]
    )
    WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER]

In plain English:
1. Filter 6 your rows and use the ROW_NUMBER function 5 to add row numbers in the order you want.
2. Filter 4 (1) to return only the row numbers you want 3 on your page.
3. Sort (2) by the row number, which 2 is the same as the order you wanted (in 1 this case, by Name).

There are essentially two ways of doing 30 pagination in the database (I'm assuming 29 you're using SQL Server):

Using OFFSET

Others have explained 28 how the ROW_NUMBER() OVER() ranking function can be used to 27 perform pages. It's worth mentioning that 26 SQL Server 2012 finally included support 25 for the SQL standard OFFSET .. FETCH clause:

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

If you're using 24 SQL Server 2012 and backwards-compatibility 23 is not an issue, you should probably prefer 22 this clause as it will be executed more 21 optimally by SQL Server in corner cases.

Using the SEEK Method

There 20 is an entirely different, much faster, but 19 less known way to perform paging in SQL. This 18 is often called the "seek method" as 17 described in this blog post here.

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

The @previousScore and @previousPlayerId values are the respective 16 values of the last record from the previous 15 page. This allows you to fetch the "next" page. If 14 the ORDER BY direction is ASC, simply use > instead.

With 13 the above method, you cannot immediately 12 jump to page 4 without having first fetched 11 the previous 40 records. But often, you 10 do not want to jump that far anyway. Instead, you 9 get a much faster query that might be able 8 to fetch data in constant time, depending 7 on your indexing. Plus, your pages remain 6 "stable", no matter if the underlying 5 data changes (e.g. on page 1, while you're 4 on page 4).

This is the best way to implement 3 paging when lazy loading more data in web 2 applications, for instance.

Note, the "seek 1 method" is also called keyset paging.

LINQ combined with lambda expressions and 12 anonymous classes in .Net 3.5 hugely simplifies 11 this sort of thing.

Querying the database:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select c;

Number 10 of records per page:

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

Sorting by any column:

customers = customers.OrderBy(c => c.LastName);

Getting 9 only selected fields from server:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select new
                {
                    CustomerID = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName
                };

This creates 8 a statically-typed anonymous class in which 7 you can access its properties:

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

Results from 6 queries are lazy-loaded by default, so you 5 aren't talking to the database until you 4 actually need the data. LINQ in .Net also 3 greatly simplifies updates by keeping a 2 datacontext of any changes you have made, and 1 only updating the fields which you change.

Actually, LINQ has Skip and Take methods 2 which can be combined to choose which records 1 are fetched.

Check those out.

For DB: Pagination In SQL Server 2005

Oracle Solution:

select * from (
    select a.*, rownum rnum from (
        YOUR_QUERY_GOES_HERE -- including the order by
    ) a
    where rownum <= MAX_ROW
 ) where rnum >= MIN_ROW

0

There are a few solutions which I use with 10 MS SQL 2005.

One of them is ROW_NUMBER(). But, personally, I 9 don't like ROW_NUMBER() because it doesn't 8 work for big results (DB which I work on 7 is really big -- over 1TB data running thousands 6 of queries in second -- you know -- big 5 social networking site).

Here are my favourite 4 solution.

I will use kind of pseudo code 3 of T-SQL.

Let's find 2nd page of users sorted 2 by forename, surname, where each page has 1 10 records.

@page = 2 -- input parameter
@size = 10 -- can be optional input parameter

if @page < 1 then begin
    @page = 1 -- check page number
end
@start = (@page-1) * @size + 1 -- @page starts at record no @start

-- find the beginning of page @page
SELECT TOP (@start)
    @forename = forename,
    @surname = surname
    @id = id
FROM
    users
ORDER BY
    forename,
    surname,
    id -- to keep correct order in case of have two John Smith.

-- select @size records starting from @start
SELECT TOP (@size)
    id,
    forename,
    surname
FROM
    users
WHERE
    (forename = @forename and surname = @surname and id >= @id) -- the same name and surname, but bigger id
    OR (forename = @forename and surname > @surname) -- the same name, but bigger surname, id doesn't matter
    OR (forename > @forename) -- bigger forename, the rest doesn't matter
ORDER BY
    forename,
    surname,
    id

There is a discussion about this Here

The technique 15 gets page number 100,000 from a 150,000 14 line database in 78ms

Using optimizer knowledge 13 and SET ROWCOUNT, the first EmployeeID in 12 the page that is requested is stored in 11 a local variable for a starting point. Next, SET 10 ROWCOUNT to the maximum number of records 9 that is requested in @maximumRows. This 8 allows paging the result set in a much more 7 efficient manner. Using this method also 6 takes advantage of pre-existing indexes 5 on the table as it goes directly to the 4 base table and not to a locally created 3 table.

I am afraid I am not able to judge 2 if it is better than the current accepted 1 answer.