When I need to display a potentially large number of rows on a screen, I often need to show them in pages. This is called paginating the rows.
For example, if I'm showing bank transactions, I might want to show 25 per page. The challenge is that I also need to be able to ask for a specific page number. So my query becomes: give me page 7 where there are 25 rows per page. In that case, I want to skip the first 150 rows, then ask for the next 25 rows.
Now I could get SQL Server to send me all the rows at once, and I could just choose which rows to display. If there are many thousands though, that could be really wasteful, mostly in transferring all that data from SQL Server to my application. And if I want to only show a single page at a time, I really don't want to get all the rows every time and then just throw most of them away.
While we could do this with T-SQL in all versions of SQL Server, it was messy. I was really pleased when SQL Server 2012 gave us an option to help with this. It came in the form of extra clauses to the ORDER BY clause.
Take a look at the following query:
It's quite elegant. It says to skip the first 150 rows, then return the next 25 rows, exactly as we wanted. To get any particular page, we just subtract one from the page, multiply by the number of rows per page, and use that as the offset:
OFFSET (@PageNumber – 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
Note that this option isn't really a performance enhancement but that's a story for another day. I like the fact that it is declarative though. You are telling SQL Server what you want, not how to do it. That's almost always a good thing.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
2 thoughts on “T-SQL 101: #17 Paginating rows returned from SQL Server T-SQL queries”
Hey Greg, a very important topic, especially when it comes to API development.
One thing that is often not realized is that when you do load an entire table into memory to filter the 25 rows you want to return, if this table is of a significant size, it can really make a mess of your buffer cache, thereby not only impacting the current operation, but potentially many operations to come as SQL re-loads the buffer cache with the data that was ejected to service queries that would otherwise have had a higher cache hit ratio.
Looking at this (http://www.mssqlgirl.com/paging-function-performance-in-sql-server-2012.html) analysis of different paging options, it does kind of explore the performance issue you hint at with OFFSET and FETCH NEXT in this post. I do like the explicit syntax of OFFSET and FETCH NEXT, but am always in search of an optimal solution. Do you have a better performing alternative, or do we need to sacrifice performance in the name of elegance?
Hi Scott, yes buffering anything large at the server end is never clever. Same thing applies to server-side cursors, just that's tempdb that gets clobbered instead.
As for pagination, I can often get something to perform better by hand-crafting it. However, I like the clarity of OFFSET and FETCH NEXT, and more importantly, it's easy to see how Microsoft can easily improve the performance of declarative statements like this. When you tell them what you're trying to achieve (rather than how to do it), they have much more room to improve things. My hand-crafted one isn't going to automagically get better over time.