SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server vs Oracle: Pagination

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.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...