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

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

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.

We will consider performance different in future posts.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

17 thoughts on “SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative

  1. 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,

    Like

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

    Like

  3. Pingback: SQL SERVER – Server Side Paging in SQL Server 2011 Performance Comparison Journey to SQL Authority with Pinal Dave

  4. 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?

    Like

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

    Like

  6. Pingback: SQL SERVER – Server Side Paging in SQL Server 2011 – Part2 Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Server Side Paging in SQL Server CE (Compact Edition) « Journey to SQLAuthority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s