TOP N与Row_Number()分页因Order by排序规则不同引发的bug
本文导语: 使用SQL Server 2005的Row_Number()函数分页，使分页变得简单和高效。 在一个系统核心表（百万级数据量）分页时为提高效率采用了Row_Number()函数和TOP N相结合的分页方法。 具体实现：取第1页数据时语句为“select top pagesize column1,colum...
使用SQL Server 2005的Row_Number()函数分页，使分页变得简单和高效。
在一个系统核心表（百万级数据量）分页时为提高效率采用了Row_Number()函数和TOP N相结合的分页方法。
具体实现：取第1页数据时语句为“select top pagesize column1,column2 from tablename order by columnname”，取第1页以后的数据时语句为“select * from (select column1,column2,ROW_NUMBER() over(order by columnname) as rownum from tablename) T where T.rownum between startnum and endnum”。看似是没什么问题，都是根据columnname排序的，绝大部情况也确实没问题，但遇到极少数特别情况，问题就暴露出来了。（第一页用TOP N取数据是不是真的比用Row_Number()快，并没有找到理论证明，而且我测试的结果是用Row_Number()快些，希望这方面能得到朋友的指导。）
问题：显示给用户的记录是根据时间倒序的，陆陆续续收到用户的反映“第一页和第二页出现相同的记录”、“有的记录没有出现在任何页”，但这种现象是极其个别的，而且出现的时间都很短暂很难重现。
后来接到一用户反映，马上查看该用户数据，惊奇的发现排序字段columnname有相同的值，而且用TOP N取第1页数据和用Row_Number()函数取第1页数据时取出来的数据不同，这样就可以解释为什么有的记录出现在两个页面有的记录没有出现了。
至于出现时间很短暂，因为排序字段columnname会被频繁的更新。
测试两种取第1页数据的方法：
--1.创建测试表
CREATE TABLE TestOrder(
[id] [int] IDENTITY(1,1) NOT NULL,
[refTime] [datetime] NOT NULL,
CONSTRAINT [PK_TestOrder] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--2.插入测试数据
declare @i int
set @i = 0
while @i < 50
begin
insert into TestOrder(refTime) values(getdate())
set @i = @i + 1
end
--通过top取前5条数据
select top 5 * from TestOrder order by refTime
--通过row_number()取前5条数据
select * from (
select *,ROW_NUMBER() over(order by refTime) as rownum from TestOrder
) T where T.rownum between 1 and 5
--5.删除测试表
drop table TestOrder
通过TOP N取出来的前5条数据：
6 2010-06-21 23:52:59.123
5 2010-06-21 23:52:59.123
4 2010-06-21 23:52:59.123
3 2010-06-21 23:52:59.123
2 2010-06-21 23:52:59.123
通过Row_Number()取出来的前5条数据：
1 2010-06-21 23:52:59.123 1
2 2010-06-21 23:52:59.123 2
3 2010-06-21 23:52:59.123 3
4 2010-06-21 23:52:59.123 4
5 2010-06-21 23:52:59.123 5
可见，但RefTime相同时，两者取出来的第1页数据是不同的，Row_Number() over(order by)和where后的order by产生的排序规则并不完全相同，不能简单的将二者等同起来，结合在一起使用可能会造成意想不到的bug。
希望以上的分析对遇到相同问题的朋友，有所帮助或启发。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。