SQL Server Denali – Pagination using ORDER BY
-
Posted on November 30, 2010 by Derek Dieter
-
0
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.
- Comments (RSS)
- Trackback
- Permalink
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
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?
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
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.
Hi Pavel,
All indications are pointing toward you not having SQL Server Denali installed (SQL 2011). Do you?
Derek
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
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?
