在SQL Server 2005中编写分页查询的最佳方法是什么？

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?

我知道在SQL Server 2005中编写分页查询的几种方法。但我不知道它们的性能。哪一个是编写分页SQL查询的最佳和最优化的方法?

1. Using Row_Number()
2. Using Partition by

使用分区

3. Using Temporary tables

使用临时表

4. Using Nest Top N with Order by

使用Nest Top N和Order by

5. Some other way?

还有其他方法吗?

1 个解决方案

#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.

根据我的经验,它使用ROW_NUMBER(),只需确保orderby字段是聚集索引,这样您就不必为每个查询上的数据集排序付出代价。