What is the most optimized way to write a paged query in SQL Server 2005?

Question

I know several ways of writing paged query in SQL Server 2005. But I have no idea about their performance. Which one is the best and most optimized way for writing paging SQL queries?

1. Using Row_Number()
2. Using Partition by
3. Using Temporary tables
4. Using Nest Top N with Order by
5. Some other way?

Answer 1

In my experience it's using ROW_NUMBER(), just make sure the orderby field is the clustered index so you won't have to pay penalties for sorting the dataset on each query.