SQL Server often uses a paging method to summarize

SQL Server often uses a paging method to summarize

The following demo sample summarizes that SQL Server database is frequently used for paging methods and is only for learning references

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       ORDER by t.s_id DESC              --Total number of data bars       SELECT @TotalCount = count (*) from Stuinfo s WHERE s.s_name like ('% ' [email protected]+ '% ') C14/>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 infer 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  (      SELECT TOP ((@PageIndex-1) * @PageSize) s.s_id From Stuinfo s             WHERE s.s_name like (' percent ' [email protected]+ '% ')             order by s.s_id DESC  )  ORDER by s.s_id DE SC       --Total number of data bars     SELECT @Count =count (*) from Stuinfo s WHERE s.s_name like ('% ' [email protected]+ '% ')   Endgo

C, paging using the Skip and take combinations of Linq:

        <summary>       //pagination        //</summary>       //<param name= "key" > Query keyword</param>       //<param name= "Pageind Ex "> page index </param>       //<param name=" pageSize "> Page size </param>    & nbsp  //<param name= "PageCount" > Total pages </param>       //<returns></returns  >        public ilist<ea_script> fuzzypaging (String key, int pageIndex, int pageSize, ref int PageCount)         {            var query = from E in DC. ea_script                        where e.fname.contains (key) &N Bsp                       after e.id descending      & nbsp         &NBSP;       Select e;           /Total pages             PAG Ecount = query. Count ()% PageSize = = 0?                (query. Count ()% pageSize): query. Count ()/pageSize + 1;            return query. Skip (pageIndex-1). Take (pageSize). ToList ();       }

If there is, better way to welcome to share!

Expand: When paging, can improve query efficiency with the help of temporary table and with AS statement

Demo sample with AS statement:

DECLARE @SearchKey Nvarchar  --query Keywordwith t as (      SELECT * from Stuinfo s             WHERE s.s_name like ('% ' [email p rotected]+ '% '))

Temporary Table Statement Demo sample:

DECLARE @SearchKey Nvarchar  --Query Keywordselect * into #temp2 from (       SELECT * from Stuinfo s WHERE s.s_name like ('% ' [email protected]+ '% ')) U

MySQL Paging

SELECT u.* from users as ULIMIT ($pIndex-1) * $pSize, $pIndex * $pSize;

SQL Server often uses a paging method to summarize