Today’s topic is Pagination. Paging is a really important feature for web pages and applications. Without it you’d be passing large amounts of data to the application and expecting the application code to figure out which rows it needed to display.

Thankfully, someone smart came up with a way to do this on the database so you’re not returning gigs and gigs of data to the webserver to sort through.

SQL Server

Pagination in SQL Server prior to SQL Server 2012 was a pain. Thankfully the OFFSET clause was introduced with 2012 and now pagination is a breeze. Let’s examine the code:

SELECT AddressID, City, PostalCode
FROM Person.Address
ORDER BY AddressID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

An ORDER BY clause must be specified to use OFFSET. You can choose to Fetch next [n] or not. Specifying the FETCH NEXT [n] will limit the rows returned to [n] while using OFFSET without a FETCH NEXT will only omit the first [n] rows your offset by. In this case 10 rows at the front would be ignored.

Oracle

Paging in Oracle uses the ROWNUM function. ROWNUM is used for other tasks like getting the top [n] rows but can easily be used for pagination as well. Let’s examine the code:

SELECT table_name FROM (SELECT table_name, rownum rnum FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 20) WHERE rnum > 10;

To use rownum for pagination you’ll want to nest derived tables to get your result. This can easily be used by an application to programmatically get the rows you want.

Conclusion

In my opinion, the OFFSET FETCH feature of SQL Server is much easier to use; however, it took Microsoft a very long time to add this to SQL Server and rownum has existed in Oracle for more than 20 years. No matter which you’re using both have the same outcome and I hope this article helps you understand the differences between the two platforms.

You can find more of my posts at my blog: SQLTechBlog.com. If you like the information you found here, please follow me at LinkedIn, Twitter, and Instagram.