SQL SERVER – Server Side Paging in SQL Server 2012 – A Better Alternative

Ranking has improvement considerably from SQL Server 2000 to SQL Server 2005/2008 to SQL Server 2012.

Here is the blog article where I wrote about SQL Server 2005/2008 paging method SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table. One can achieve this using OVER clause and ROW_NUMBER() function.

Now SQL Server 2011 has come up with the new Syntax for paging. Here is how one can easily achieve it.

USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
@PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

I consider it good enhancement in terms of T-SQL. I am sure many developers are waiting for this feature for long time.

SQL SERVER - Server Side Paging in SQL Server 2012 - A Better Alternative denalipaging

We will consider performance different in future posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – What the Business Says Is Not What the Business Wants
Next Post
SQL SERVER – Server Side Paging in SQL Server 2012 Performance Comparison

Related Posts

13 Comments. Leave new

  • prasad yangamuni
    December 15, 2010 9:46 am

    Hello Pinal,
    Awesome article, This was really nice feature…
    this feature will reduce lot of developer burden…

    Reply
  • What is the difference with respect to the performance between different ways of paging i.e using CTE , using SubQuery and latest using offset

    Reply
  • Luciano Evaristo Guerche (Gorše)
    December 15, 2010 6:23 pm

    Hi Pinal,

    Should it not be the following?

    FETCH NEXT @RowsPerPage ROWS ONLY

    Why is it fixed at 10? Does FETCH NEXT not allow using a variable?

    Another doubt which arose to me is:

    Shouldn’t it be:

    OFFSET (@PageNumber * @RowsPerPage) + 1 ROWS

    instead of

    OFFSET @PageNumber*@RowsPerPage ROWS

    ?

    Thanks and regards,

    Reply
  • Luciano Evaristo Guerche (Gorše)
    December 15, 2010 6:28 pm

    In your example, I am guessing pointer will be moved to record #50 (OFFSET) then FETCH NEXT (I am implying NEXT do not include current record #50) will get records from #51 to #60, so your example would be correctly stated, but I am not completely sure about it.

    Cheers.

    Reply
  • There’s no such thing as SQL Server 2011. Not yet anyway.

    Reply
  • Luciano Evaristo Guerche (Gorše)
    December 15, 2010 9:49 pm

    I was reading “Klaus Aschenbrenner – 2 of my favorite new T-SQL features in SQL Server Denali (SQL 11)” and noted he used:

    OFFSET (@PageSize * (@CurrentPage – 1)) ROWS

    Then I realized/recalled page 1 does not start at 11 but at 1 :-)

    Cheers.

    Reply
  • Hi Pinal,
    Nice feature, could you explain the syntax a little bit more
    What does the OFFSET command do.

    OFFSET @PageNumber*@RowsPerPage ROWS
    FETCH NEXT 10 ROWS ONLY

    Reply
  • José F. Romaniello
    December 16, 2010 8:00 pm

    About the feature; it is an important feature, Sql 2005 has overcome this with an ugly syntax. Most of the relational databases does support this from long time ago.

    Sincerely, this new syntax sucks less, but still sucks.
    Are you seriously with this one???:

    OFFSET @PageNumber*@RowsPerPage ROWS”
    FETCH NEXT 10 ROWS ONLY

    6 keywords!!!!!!! Postgresql and MySql just use TWO keywords for doing exactly OFFSET X LIMIT X. simpler than that?

    Reply
  • Hi Pinal,

    Do you ever have any posts that go beyond the basic MSDN guide on a feature. And really, how relevant is this to now, when only Microsoft and God knows when Sql Server 2011 will be released.

    I have you on my RSS feed and its time I remove your nuisance posts from my reading.

    Reply
  • Sql is learnt and for read we r feeling comfertable .
    And Sql is abckend language. we want store the data we can do to set a query and to set a include the the logic.

    Reply
  • Anil Kumar Sahoo
    April 26, 2011 2:17 pm

    Hi Pinal,

    Its really good Article.

    Reply
  • Hi Pinel, for small number of records, OFFSET and FETCH are a good solution, but when you are working in a lot off records (above 250k in my case), this approuch is not good enough because when @pagenumber is higher than 100, the query results time is increaded.
    Can you help me in this case?
    Best regards

    Reply

Leave a Reply

Menu