How to make paging in Sqlserver 2008 R2?

Question

I tested with two forms, but did not succeed.

select * from table OFFSET 10 ROWS

select top 10 * from table OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

Interestingly I never had to offset this version of Sqlserver, and now I’m having difficulties with something relatively simple.

Errors displayed on console:

Message 102, Level 15, Status 1, Line 3
Incorrect syntax near 'OFFSET'.
Message 153, Level 15, State 2, Line 3
Invalid Usage of the option NEXT in the FETCH statement.

I might be wrong, but I confess that I did not find answers on the site that really answer my question (which is curious, since it is something relatively simple).

Answer 1

There are two ways to do this. Let’s assume that the query you will make is this:

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, Voce should determine the total results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...may be inappropriate considering the efficiency of the result.

Now, in order to achieve a more appropriate consultation, the following model could be the most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Here it will return 1-19 lines of the original query. Now, just adapt to your code.

Answer 2

Thiago, To paginate it is necessary an ordination (ORDER BY) of the records for your case:

SELECT * FROM table 
ORDER BY 1 --Nome campo
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY