在数据库中做分页处理真是愁死人了。怀着愿望看 SQL Server 2005 的新特性，“ROW_NUMBER”进入我的眼球，而且 OVER ( Order_by_clause )，真是今人振奋的消息啊。不知其是否能想我想像的那样能很快附之应用。
备注：
- Transact-SQL Enhancements
http://www.microsoft.com/sql/2005/productinfo/top30features.asp
SQL Server 2005 provides new language capabilities for developing scalable database applications. These enhancements include error handling, recursive query capabilities, relational operator PIVOT, APPLY, ROW_NUMBER and other row ranking functions, and more. - Calculating Row Numbers in SQL Server 2005
http://www.winnetmag.com/SQLServer/Article/ArticleID/42302/42302.html
SELECT
? ROW_NUMBER() OVER(ORDER BY orderid) AS rownum,
? orderid, CONVERT(varchar(10), orderdate, 120) AS orderdate,
? empid, custid, qty
FROM dbo.Orders AS O1
ORDER BY orderid
Without an index, the ROW_NUMBER() function in SQL Server 2005 scans the table only once, then sorts the rows to calculate all the row numbers. The solution in SQL Server 2000 scans the table once per each base row. I already mentioned that it took the SQL Server 2000 query more than half an hour to run on my laptop with 100,000 rows in the table. In comparison, the performance of the SQL Server 2005 query with the same table is simply astonishing only 1 second.
现在我要做的是，在 SQL Server 2005 未推出或未证明可以稳定服务前，现在的分页如何处理……，还像以前的方法做吗？在 .NET 2.0 里已经不时髦了。
2004年10月19日 10:44 沙发
没找到 SQL Server 2005 开发人员版的下载。
发现我的机器里有个也许是装 .NET 2.0 Beta 时留下的 SQLServer 2005 Express。试用了一下。
发现没法这样用:
SELECT ROW_NUMBER() OVER (ORDER BY aaa) AS rownum, *
FROM dbo.Table1
WHERE ROW_NUMBER() OVER (ORDER BY aaa) >= 11
AND ROW_NUMBER() OVER (ORDER BY aaa) <= 20
ORDER BY KEYID
想实现按 rownum 的分页只能这样:
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY aaa) AS rownum, *
FROM dbo.Table1
) a
WHERE rownum >= 11 and rownum <= 20
这样的话，看上去就不是那么帅了，不知大数据量时速度会怎样。不过再怎样，也比三个 TOP 查询嵌套好一些了。