There’s a good article here by Frank Kerrigan on a way to implement paging in SQL Server 2005 by using Common Table Expressions (CTE’s).
What are CTE’s? This is a new feature of SQL Server 2005 that helps you eliminate temp tables and cursors in your queries. You can generate some pretty amazing queries using recursive CTE’s, but that’s not what Frank is trying to demonstrate. Anyway, they can be used similarly to a derived table.
Here’s the syntax:
[ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )