Paged Results in SQL Server

A common requirement for applications is to display a list – that can range from products to search results. While showing all entries works for small lists, it is not that useful for lists with hundreds of entries. In this case you should use paging and only show 10 or 20 entries at a time. The user sees the first part of entries and then can switch to the next page of the list to get more entries.
This approach is faster, reduces the workload on your systems and the user isn’t drowning in data. Let’s look how you can implement paging in applications that use SQL Server.

 

SQL Server 2012+

SQL Server needed some time to offer a simple way to get paging working. If you can use SQL Server 2012 or later, this simple syntax gets you a paged result:

With OFFSET you specify how many rows should be skipped. If you skip 0 rows you get the first ones in the list. FETCH NEXT is used to specify how many rows you want.
The syntax above uses the placeholders @offset and @rowsPerPage to use this query in a prepared statement. To get the first 20 entries you can use this code:

If you use Dapper, this command does the same with much less code:

 

Older versions of SQL Server

Without the new syntax, you need to use a far more complex query to get your paged results from SQL Server:

 

Conclusion

The syntax for SQL Server 2012 and newer is much simpler to understand. But when you can’t use a current version of SQL Server you still are able to page your results on the database server.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.