Sqlserver can also be Based on Pseudo-column paging!

Like Oracle, SQL Server 2005 is followed by the row_number () pseudo-column function. You can use a subquery to implement data paging.

Orcale paging: Let's take a look at this article. Let's take a look at how Oracle Stored Procedure dynamic SQL queries get query pages!

First, give it a try.

 SELECT * FROM 
 (
   SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test 
 )   as temp where Row between  1 and 10

It seems okay ......

Rewrite it:

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;

Use the with statement to rewrite the statement and simulate the stored procedure written by others.

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

It is said that writing in this way can improve the statement efficiency.

Under the condition of 1000 normal query statements, the response time is almost ignored!

Test the stored procedure:

It should not matter whether it is up or down... continue to study later!