用 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
http://www.531.hk/228.aspx