Summary of common paging methods for SQL Server

Summary of common paging methods for SQL Server

The following example summarizes the common paging methods for SQL Server databases, which are used only for learning reference

A, using RowNumber and between and combination paging:

/********** using RowNumber and between and combination paging **********/create PROC proc_fuzzysearchandpaging@pageindex int,           -- Page index @pagesize int,            --page size @searchkey Nvarchar (Ten),  --query keyword @totalcount int OutPut    --Total number of data bars as  BEGIN       --Query the current page data       select * FROM (           select *,[no]=row_number () over (ORDER by s.s_id DESC) from Stuinfo s                  WHERE s.s_name Like ('% ' [email protected]+ '% ')       ) T        WHERE T.[no] between @pageSize * (@pageIndex-1) +1 and @pageIndex * @pageSize C11/>order by t.s_id DESC              --Total number of data bars       SELECT @TotalCount = count (*) from Stuinfo s WHERE s.s_name like ('% ' [email pro tected]+ '% ')  Endgo

B, using the TOP and not in combination paging:

/********** using TOP and not in combination paging **********/create PROC proc_fuzzysearchandpaging2@pageindex int,             --current page index @pagesize in T,              --number of data bars per page @fuzzykey Nvarchar,     --fuzzy matching keyword @count int OUTPUT           --Total number of data bars (used to determine how many pages to divide) as  BEGIN      SELECT TOP (@PageSize) * from Stuinfo s  WHERE s.s_name like ('% ' [email protected]+ '% ') and s.s_id not in  (      Selec T TOP ((@PageIndex-1) * @PageSize) s.s_id from Stuinfo s             WHERE s.s_name like ('% ' [email protected]+ '% ')             ORDER by s.s_id ASC  )  ORDER by s.s_id ASC       --Total number of data bars     SELECT @Count =count (*) from Stuinfo s WHERE s.s_name like ('% ' [email protected]+ '% ')   Endgo

If so, a better way to get out and share!

Extensions: When paging, you can provide query speed with full use of temporal tables and with as statements

Example with AS statement:

DECLARE @SearchKey Nvarchar  --query keyword with t as (      SELECT * from Stuinfo s             WHERE s.s_name like ('% ' [email prote cted]+ '% '))

Example of a temporary table statement:

DECLARE @SearchKey Nvarchar  --Query keyword SELECT * into #temp2 from (       select * from Stuinfo s WHERE s.s_name like ('% ') [Email protected]+ '% ')) u