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.
We will consider performance different in future posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Hello Pinal,
Awesome article, This was really nice feature…
this feature will reduce lot of developer burden…
What is the difference with respect to the performance between different ways of paging i.e using CTE , using SubQuery and latest using offset
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,
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.
There’s no such thing as SQL Server 2011. Not yet anyway.
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.
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
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?
It is ANSI valid syntax.
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.
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.
Hi Pinal,
Its really good Article.
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