ROW_NUMBER() 利用临时表、索引分页

 

ALTER procedure [dbo].[sp_Common_GetDataPaging_ReturnDataCount] 
( 
@PageNumber int, --当前的页码 
@PageSize int, --每页显示的数据量 
@DataCount int out --总数据量 
) 
as 
begin


declare @BNum int 
declare @ENum int 
set @BNum = (@PageNumber-1)*@PageSize+1 
set @ENum = @PageNumber*@PageSize



select ROW_NUMBER() over( order by id ) as Sequence,* into  #temp from  tb_order  
CREATE INDEX mycolumn_index ON #temp (Sequence)
select *from #temp  WHERE Sequence BETWEEN @BNum  and @ENum   ORDER BY Sequence

end