Large data storage page

Five common stored procedures:

1. Use select top and select not in for paging. The Code is as follows:

1 create procedure proc_paged_with_notin -- use select top and select not in
2 (
3 @ pageindex int, -- page index
4 @ pagesize int -- number of records per page
5)
6As
7 begin
8 set nocount on;
9 declare @ timediff datetime -- time consumed
10 declare @ SQL nvarchar (500)
11 select @ timediff = getdate ()
12 set @ SQL = 'select top '+ str (@ pageSize) +' * from tb_TestTable where (ID not in (select top '+ str (@ pageSize * @ pageIndex) + 'id from tb_TestTable order by id ASC) order by id'
13 execute (@ SQL) -- Because select top is directly connected to parameters without technical support, it is written as a string @ SQL
14 select datediff (MS, @ timediff, GetDate () as time consumed
15 set nocount off;
16end

2. Use select top and select max (column key)

1 create procedure proc_paged_with_selectMax -- use select top and select max (column)
2 (
3 @ pageIndex int, -- page index
4 @ pageSize int -- number of page records
5)
6as
7 begin
8 set nocount on;
9 declare @ timediff datetime
10 declare @ SQL nvarchar (500)
11 select @ timediff = Getdate ()
12 set @ SQL = 'select top '+ str (@ pageSize) +' * From tb_TestTable where (ID> (select max (id) from (select top '+ str (@ pageSize * @ pageIndex) + 'id From tb_TestTable order by id) as TempTable) order by id'
13 execute (@ SQL)
14 select datediff (MS, @ timediff, GetDate () as time consumed
15 set nocount off;
16end

3. Use select top and intermediate variable -- this method is tested together because someone on the internet says it works best.

1 create procedure proc_paged_with_Midvar -- use the maximum ID value of ID> and the intermediate variable
2 (
3 @ pageIndex int,
4 @ pageSize int
5)
6as
7 declare @ count int
8 declare @ ID int
9 declare @ timediff datetime
10 declare @ SQL nvarchar (500)
11 begin
12 set nocount on;
13 select @ COUNT = 0, @ ID = 0, @ timediff = getdate ()
14 select @ COUNT = @ count + 1, @ ID = case when @ count <= @ pagesize * @ pageindex then Id else @ ID end from tb_testtable order by ID
15 set @ SQL = 'select top '+ STR (@ pagesize) +' * From tb_testtable where ID> '+ STR (@ ID)
16 execute (@ SQL)
17 select datediff (MS, @ timediff, getdate () as time consumed
18 set nocount off;
19end

4. Use row_number () to add an index to the data row in SQL Server 2005.

1 create procedure proc_paged_with_rownumber -- use row_number () of SQL 2005 ()
2 (
3 @ pageIndex int,
4 @ pageSize int
5)
6as
7 declare @ timediff datetime
8 begin
9 set nocount on;
10 select @ timediff = getdate ()
11 select * from (select *, Row_number () over (order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank> @ pageSize * @ pageIndex and IDRank <@ pageSize * (@ pageIndex + 1)
12 select datediff (MS, @ timediff, getdate () as time consumed
13 set nocount off;
14end

5. Use the temporary table and row_number

1 create procedure proc_cte -- use a temporary table and row_number
2 (
3 @ pageindex int, -- page index
4 @ pagesize int -- number of page records
5)
6As
7 set nocount on;
8 declare @ ctestr nvarchar (400)
9 declare @ strsql nvarchar (400)
10 declare @ datediff datetime
11 begin
12 select @ datediff = getdate ()
13 set @ ctestr = 'with table_cte
14 (select ceiling (Row_number () over (order by id asc)/'+ str (@ pageSize) +') as page_num, * from tb_TestTable )';
15 set @ strSql = @ ctestr + 'select * From Table_CTE where page_num = '+ str (@ pageIndex)
16end
17 begin
18 execute sp_executesql @ strSql
19 select datediff (MS, @ datediff, GetDate ())
20 set nocount off;
21 end