SQL Server Denali – Query Paging

Recently, we wrote about a new feature of SQL Server Denali called sequences. (Denali is the code name for the next version of Microsoft SQL Server. No release date has been announced, but it will presumably be released late this year or early next year.)

Today, we’d like to take a look at another feature of Denali called query paging. A challenge that often arises is how best to display a lengthy set of data elements to a user. For example, suppose we have a log file with millions of rows and we want to show our user a grid view of rows from this lengthy table. Obviously, we don’t want to have to retrieve all of these rows just to show some subset of them to the user. Displaying a “page” of rows at a time to the user is called “paging”.

ASP.NET supports paging intrinsically with the GridView control. Annoyingly, its behavior is that for each page viewed, it will re-run the query. So if your query is something like SELECT * FROM activity_log ORDER BY log_id and there are millions of rows in the activity_log table, your GridView will hammer your database.

There are plenty of solutions for working around this problem. One idea is to simply force your users to filter their data – in other words, they cannot display an unfiltered list of log entries and must enter a date. Another idea is to use the TOP keyword to restrict the number of rows returned. So if we are on page four of the results and are displaying 100 rows per page, we might use the query SELECT TOP 400 * FROM activity_log ORDER BY log_id to limit the results returned to 400 rows, 300 of which will be discarded. For example, we might “cheat” by adding this logic to our page:

This function will change our select command to always add an extra five pages worth of data (by default the GridView allows you to navigate to five pages on either side of your current page) and runs much more quickly than simply selecting every row in the table.

Fortunately, in Denali, Microsoft gives us two new enhancements to the ORDER BY syntax – OFFSET and FETCH. To grab the fourth page of rows now, instead of the above query, we will use:

SELECT TOP 400 *FROM activity_logORDER BY log_idOFFSET 300 ROWSFETCH NEXT 100 ROWS ONLY

This new feature should allow us to implement paging with much cleaner code.

As you move through the thought process of needing to bring in a team of designers, programmers, and data engineers, think about the JASE eBusiness Solutions team as a perfect option for your needs. This tip and others talked about on this blog will make you glad you chose a team like ours to create the applications your business relies on.