在SQL 2008中使用大表进行有效的分页 [英] Efficient paging with large tables in 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.

这篇关于在SQL 2008中使用大表进行有效的分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！