用 SQL 2005 的 row_number() 做的分页，表上建1个聚集索引、三个非聚集索引、两个全文索引。
从 1000 万条记录中按特定条件提取1~200条，耗时在 3 秒以内。平均60个并发用户请求。
语句A:
Select * FROM
(Select ROW_NUMBER() OVER (ORDER BY [id] ASC) AS RowNum, * FROM table) AS T
Where RowNum BETWEEN X AND Y
语句B:
Select TOP 10 * FROM
(Select row_number() over(order by id) as RowNum, Company_Info.* from Company_Info) AS TempTable
Where TempTable.RowNum > 1 * 10
SQL语句如下：
Select TOP 页大小 *
FROM TestTable
Where (ID >
(Select MAX(id)
FROM (Select TOP 页大小*页数 id
FROM 表
orDER BY id) AS T))
orDER BY ID
（利用ID大于多少和Select TOP分页）效率最高
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。