A common activity in applications is to page results or record sets from a database. This is usually done on the client using the clients paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and werent very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.The Members Page on SQLTeam.com lists the first 20 members sorted by number of posts. In order to generate this it selects the entire set of members, returns it to the client and then displays the first 20 rows using ADOs paging functionality. The SELECT statement it runs looks something like this

via Server Side Paging using SQL Server 2005 – SQLTeam.com.