SQL Server 2008 paging methods?

Question

I have to work with a potentially large list of records and I've been Googling for ways to avoid selecting the whole list, instead I want to let users select a page (like from 1 to 10) and display the records accordingly.

Say, for 1000 records I will have 100 pages of 10 records each and the most recent 10 records will be displayed first then if the user click on page 5, it will show records from 41 to 50.

Is it a good idea to add a row number to each record then query based on row number? Is there a better way of achieving the paging result without too much overhead? So far those methods as described here look the most promising:

http://developer.berlios.de/docman/display_doc.php?docid=739&group_id=2899

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

Answer 1

The following T-SQL stored procedure is a very efficient implementation of paging. THE SQL optimiser can find the first ID very fast. Combine this with the use of ROWCOUNT, and you have an approach that is both CPU-efficient and read-efficient. For a table with a large number of rows, it certainly beats any approach that I've seen using a temporary table or table variable.

NB: I'm using a sequential identity column in this example, but the code works on any column suitable for page sorting. Also, sequence breaks in the column being used don't affect the result as the code selects a number of rows rather than a column value.

EDIT: If you're sorting on a column with potentially non-unique values (eg LastName), then add a second column to the Order By clause to make the sort values unique again.

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS

DECLARE @FirstId int, @FirstRow int

SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow

-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.

SELECT   @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SET ROWCOUNT @PageSize

SELECT   *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

SET ROWCOUNT 0
GO 

Answer 2

If you use a CTE with two row_number() columns - one sorted asc, one desc, you get row numbers for paging as well as the total records by adding the two row_number columns.

create procedure get_pages(@page_number int, @page_length int)
as
    set nocount on;

    with cte as
    (
        select 
            Row_Number() over (order by sort_column desc) as row_num
            ,Row_Number() over (order by sort_column) as inverse_row_num
            ,id as cte_id
        From my_table
    )
    Select 
        row_num+inverse_row_num as total_rows
        ,*  
    from CTE inner join my_table
        on cte_id=df_messages.id
    where row_num between 
        (@page_number)*@page_length 
        and (@page_number+1)*@page_length
    order by rownumber

Answer 3

Using OFFSET

Others have explained how the ROW_NUMBER() OVER() ranking function can be used to perform pages. It's worth mentioning that SQL Server 2012 finally included support 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 SQL Server 2012 and backwards-compatibility is not an issue, you should probably prefer this clause as it will be executed more optimally by SQL Server in corner cases.

Using the SEEK Method

There is an entirely different, much faster way to perform paging in SQL. This is often called the "seek method" as 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 values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY direction is ASC, simply use > instead.

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

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

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

Answer 4

Try something like this:

declare @page int = 2
declare @size int = 10

declare @lower int =  (@page - 1) * @size
declare @upper int =  (@page    ) * @size

select * from (
select 
    ROW_NUMBER() over (order by some_column) lfd,
* from your_table
) as t
 where lfd between @lower and @upper
 order by some_column

Answer 5

Here's an updated version of @RoadWarrior's code, using TOP. Performance is identical, and extremely fast. Make sure you have an index on TestTable.ID

CREATE PROC dbo.PagingTest
    @SkipRows int,
    @GetRows int
AS
DECLARE @FirstId int

SELECT   TOP (@SkipRows) 
         @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SELECT   TOP (@GetRows) *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

GO 

Answer 6

Try this

Declare @RowStart int, @RowEnd int;


SET @RowStart = 4;
SET @RowEnd = 7; 

With MessageEntities As 
(
    Select ROW_NUMBER() Over (Order By [MESSAGE_ID]) As Row, [MESSAGE_ID]
    From [TBL_NAFETHAH_MESSAGES]
)
Select  m0.MESSAGE_ID, m0.MESSAGE_SENDER_NAME,
        m0.MESSAGE_SUBJECT, m0.MESSAGE_TEXT
From MessageEntities M
    Inner Join [TBL_NAFETHAH_MESSAGES] m0 on M.MESSAGE_ID = m0.MESSAGE_ID
Where M.Row Between @RowStart AND @RowEnd
Order By M.Row Asc
GO

Answer 7

Why not to use recommended solution:

SELECT VALUE product FROM AdventureWorksEntities.Products AS product order by product.ListPrice SKIP @skip LIMIT @limit