sqlserver 也可以基于伪列分页！

与oracle一样  sqlserver 2005后出现 借助函数 ROW_NUMBER()伪列，可以简单的通过子查询实现数据分页

Orcale 分页可以看一下这一篇 简单搞一下 Oracle 存储过程动态SQL之获取查询分页！

先简单试一下

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
) 

  as temp where Row between 1 and 10

看来还行......

继续改写一下：

declare
@v_index int ,
@v_size int
begin
set @v_index = 1 ;
set @v_size = 10 ;
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test
)
as temp where Row
between (@v_index - 1 ) * @v_size + 1  

　　　　　　　 and (@v_index - 1 ) * @v_size + @v_size
end;

用with 语句再进行改写一下，模仿别人写成存储过程

create procedure proc_get_test_list
(
@p_index int ,
@p_size int
)
as
begin

with temptb as
(
select row_number() over (order by id desc) as row, * from test
)

select * from temptb where row between

(@p_index - 1 ) * @p_size + 1 and (@p_index - 1 ) * @p_size + @p_size

end

传说这样写 可以提升语句效率

普通查询语句在1000条的条件下，响应时间几乎忽略！

测试一下存储过程：

应该算不分上下吧...... 后面再继续研究！

转载于:https://www.cnblogs.com/darjuan/archive/2011/05/25/2057167.html