MSSQL an efficient SQL stored procedure page that performs efficiency tests through more than 5 million data

/****** Object: StoredProcedure [dbo].[proc_paged_2part_selectMax] Script Date: 01/06/2010 15:06:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [proc_paged_2part_selectMax] (@tblName nvARCHar (200), ---- The table to display or the join of multiple tables @fldName nvarchar(500) =The '*', ---- List of fields to display @pagesize int = 10, ---- Number of records to display @page int = 1, ---- Which page record to display @fldsort nvArchar (200) = null, ---- Sort field list or condition @sort bit = 0, ---- Sort method, 0 is ascending, 1 is descending (if multiple fields are sorted, Sort indicates the order of the last field.)' SortA Asc,SortB Desc,SortC ') @strcondition nvarchar(1000) = null, ---- Query condition, no needwhere@id nvARCHar (150), ---- Primary key of the primary table @dist bit = 0, ---- Whether to add a DISTINCT query field. Default 0 Not added /1 Add @Pagecount int = 1 output, ---- Query result Total number of pages after pages @counts int = 1 output ---- Number of queried records) AS SET NOCOUNT ON Declare @sqLTMP nvARCHar (1000) ---- Stores dynamically generated SQL statements Declare @strtmp nvarchar(1000) ---- Stores the query statements that obtain the total number of query results Declare @strid nvarchar(1000) ---- Stores the query statements Declare that obtain the ID at the beginning or end of the query @strsortType nvARCHar (10) ---- Data collation A Declare @strfsortType nvARCHar (10) ---- Data collation B Declare @sqlSelect NVARCHar (50) ---- SQL construct for the queries with DISTINCT Declare @SQLCounts nvARCHar (50) ---- SQL construct for the total queries with DISTINCTdeclare@timediff dateTime select @timediff= getDate ()if @Dist = 0 

begin 

 set @SqlSelect = 'select ' 

 set @SqlCounts = 'Count(*)' 

end 

else 

begin 

 set @SqlSelect = 'select distinct ' 

 set @SqlCounts = 'Count(DISTINCT '[+@ID+](mailto:+@ID+)') ' 

end 

if @Sort=0 

begin 

 set @strFSortType=' ASC ' 

 set @strSortType=' DESC ' 

end 

else 

begin 

 set @strFSortType=' DESC ' 

 set @strSortType=' ASC 'The end -- -- -- -- -- -- -- -- to generate the query -- -- -- -- -- -- -- -- -- here @ strTmp number of statements for query resultsif @strCondition is null or @strCondition=' '-- The display condition begin is not setset @sqlTmp = @fldName + ' From ' + @tblName 

 set @strTmp = @SqlSelect+' @Counts='[+@SqlCounts+](mailto:+@SqlCounts+)' FROM '[+@tblName](mailto:+@tblName) 

 set @strID = ' From ' + @tblName 

end 

else 

begin 

 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 

 set @strTmp = @SqlSelect+' @Counts='[+@SqlCounts+](mailto:+@SqlCounts+)' FROM '[+@tblName](mailto:+@tblName) + ' where (1>0) ' + @strCondition 

 set @strID = ' From ' + @tblName + ' where (1>0) '+ @strcondition end ---- Obtain the total number of query results -----exec sp_executesql @strTmp,N'@Counts int out '[,@Counts](mailto:,@Counts) out 

declare @tmpCounts int 

if @Counts = 0 

 set @tmpCounts = 1 

else 

 set@tmpCOUNTS = @counts -- Get the total number of pagesset@ pageCount = (@] [tmpCounts + @ pageSize - 1 (mailto: tmpCounts + @ pageSize - 1)) / @ pageSize / * * / / / / / / * * * * * * the current page is greater than the total number of pages in the last page * * /if @page>@pageCount 

 set@page= @pagecount --/*----- Data pagination 2 minute processing -------*/declare@pageIndex int -- Total number/page sizedeclare@lastCount int -- Total % page sizeset @pageIndex = @tmpCounts/@pageSize 

 set @lastcount = @tmpCounts%@pageSize 

 if @lastcount > 0 

 set @pageIndex = @pageIndex + 1 

 else 

 set@lastCount = @pagesize --//*** displays paginationif @strCondition is null or @strCondition=' '-- The display condition begin is not setif@pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 -- beginif @page=1 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType 

 else 

 begin 

 if @Sort=1 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 end 

 else 

 begin 

 set@Page = @[pageIndex-@page+1](mailto:pageIndex-@page+1) -- Data processing for the latter partif@page <= 1 -- the last page of data displayset @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else 

 if @Sort=1 

 begin 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 end 

 else- The query condition begin existsif@pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 -- beginif @page=1 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType 

 else if(@Sort=1) 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 else 

 begin 

 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' 

 +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType 

 end 

 end 

 else 

 begin 

 set@Page = @[pageIndex-@page+1](mailto:pageIndex-@page+1) -- Data processing for the latter partif@page <= 1 -- the last page of data displayset @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else if(@Sort=1) 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 

 else 

 set @strTmp=@SqlSelect+' * from ('[+@SqlSelect+](mailto:+@SqlSelect+)' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '[+@tblName](mailto:+@tblName) 

 +' where '[+@ID+](mailto:+@ID+)' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)[+@lastcount](mailto:+@lastcount) as Varchar(20)) +' '+ @ID +' from '[+@tblName](mailto:+@tblName) 

 +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' 

 +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strfsortType end end ------ Query result -----execSp_executesql @strtmp select datediff([ms,@timediff, getDate](mailto:ms,@timediff, getDate)()) as time --print @strtmp SET NOCOUNT OFF - In my tests, this is the highest performing stored procedure page I've ever seen! Almost twice as fast as some of sql2005's ROW_NUMBER() algorithms, check it outCopy the code