Skip to main content
Paging in SQL Server 2005
Entry Date: Rate:
Site:
Keywords:
RSS Description:
Developers and database administrators have long debated methods for paging recordset results from Microsoft SQL Server, trying to balance ease of use with performance. The simplest methods were less efficient because they retrieved entire datasets from SQL Server before eliminating records which were not to be included, while the best-performing methods handled all paging on the server with more complex scripting. The ROW_NUMBER() function introduced in SQL Server 2005 provides an efficient way to limit results relatively easily.
Comments:

There are several articles available about how to perform custom paging using a GridView or a Repeater, that's fine if you have to work with that controls, but in some cases you need to use it in your own custom controls or other scenarios

This article explains you a good performing solution for paging: it uses SQL Server 2005 new function ROW_Number, bad news of this way of performing queries is that you will have to use Dynamic SQL in your stored procs if you need to use dynamic sorting (ORDER BY limitations... ).