在SQL 2008中使用大表进行有效的分页

本文介绍了在SQL 2008中使用大表进行有效的分页的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

用于具有> 1,000,000行并且可能还有更多行的表！

for tables with > 1,000,000 rows and possibly many many more !

我自己没有进行任何基准测试，所以想征询专家的意见.

haven't done any benchmarking myself so wanted to get the experts opinion.

看了row_number()上的一些文章，但似乎对性能有影响

Looked at some articles on row_number() but it seems to have performance implications

还有哪些其他选择/替代方案?

What are the other choices/alternatives ?

推荐答案

我们使用row_number()可以产生很大的效果，并且实际上没有任何性能问题.分页查询的基本结构如下:

We use row_number() to great effect and there hasn't really been any performance issues with it. The basic structure of our paginated queries looks like this:

WITH result_set AS ( SELECT ROW_NUMBER() OVER (ORDER BY <ordering>) AS [row_number], x, y, z FROM table WHERE <search-clauses> ) SELECT * FROM result_set WHERE [row_number] BETWEEN a AND b

对于具有1,000,000行以上的表，它对我们来说很好用.

It works fine for us on tables with > 1,000,000 rows.