SQL Server Denali – Pagination using ORDER BY

SQL Server Denali (2012) now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example:

[cc lang=”sql”]
SELECT *
FROM HumanResources.Employee E
ORDER BY E.EmployeeID ASC
OFFSET 55 ROWS
FETCH NEXT 30 ROWS ONLY;
[/cc]

Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following:

[cc lang=”sql”]
CREATE PROCEDURE dbo.spGetEmployees
(
@StartRow int = 1,
@RowCount int = 10
)
AS
BEGIN

SELECT *
FROM HumanResources.Employee E
ORDER BY E.EmployeeID ASC
OFFSET @StartRow ROWS
FETCH NEXT @RowCount ROWS ONLY;

END
[/cc]

As you can see, this new syntax makes pagination much simpler than having to roll your own in previous versions.

7 comments
Mouss 29 Aug 2015 at 5:51 am

Hi David,I wish I could say I had a rooster for an alarm clock. I’ve been doing this for so long that I ualulsy wake up without an alarm. I don’t know if that is good or bad. Although I will admit that my son (who is now 7) was a terrible sleeper as a baby, and it is because of his schedule that I found this 4am writing niche. He would wake up between 1-2 am and stay wide awake for 2 hours. He’d fall asleep at 4, but by then I had my second wind and I couldn’t go back to sleep. So, I guess my son was my special pest like your rooster Thanks so much for stopping by. I actually live quite near Maine. South Berwick is only about a 10 minute jaunt for me. My sister and my mother and my cousin all live in Kittery. You and I live in a beautiful part of the country, don’t we?Have a wonderful weekend and 4th of July

Nic 03 Jun 2011 at 6:32 am

Hi,

Good post so thanks, I went off and built my own example and it’s certainly neater than other current methods, however when I compared it to an existing method of a CTE using a ROW_NUMBER and then selecting from the CTE where the row number is between 10 and 20, I noticed that there is no performance benefit what so ever (on a table with 10,000 records and no index). Is this new functionality more of a ‘pretty code’ than a performance gain?

Derek Dieter 16 Aug 2011 at 10:29 am

Hi Nic,

Thanks for the information. I will have to do some performance test myself also. It may be that 10,000 records is too small to test with. Did you flush the buffer cache between methods?

Thanks,
Derek

Pavel 07 Jan 2011 at 11:11 am

When I use this query
SELECT *
FROM HumanResources.Employee E
ORDER BY E.EmployeeID ASC
OFFSET 55 ROWS
FETCH NEXT 30 ROWS ONLY;
I got this error as:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘OFFSET’.
Msg 153, Level 15, State 2, Line 4
Invalid usage of the option NEXT in the FETCH statement.

Derek Dieter 07 Jan 2011 at 3:12 pm

Hi Pavel,

All indications are pointing toward you not having SQL Server Denali installed (SQL 2011). Do you?

Derek

Alessandra 29 Aug 2015 at 8:19 am

Klong long ago, when i was a young farm boy, i used to get up at 4 AM to milk and feed the cows. the old antediluvian resotor was my alarm clock. do you have a special pest that wakes you David in Maine USA

Moises 29 Aug 2015 at 6:10 am

Hey! Sorry to post this here, but I couldn’t find a bteter place!I tried to email you the other day and it bounced back. It was the comcast.net email address I had in my address book. Did you get a new one or something?I’m guessing you knew I wanted to beg a favor & changed it, didn’t you?

Featured Articles

 Site Author

  • Thanks for visiting!