Comparison of several common SQL paging Methods

Paging is very important and will be encountered during the interview. Let's review and summarize it.

1. Create a test environment (it takes about 5 minutes to insert 1 million data records ).

 Create Database dbtest 
 Use dbtest 

 -- Create a test table
Create Table pagetest 
 ( 
 Id int identity (1, 1) not null, 
 Col01 int null, 
 Col02 nvarchar (50) null, 
 Col03 datetime null 
 ) 

-- 10 thousand record set
Declare @ I int 
 Set @ I = 0 
 While (@ I <10000) 
 Begin 
 Insert into pagetest select cast (floor (RAND () * 10000) as INT), left (newid (), 10), getdate () 
 Set @ I = @ I + 1 
 End 

2. Several Typical paging SQL statements. The following example shows 50 lines per page, 198*50 = 9900, and the data on 199th pages is obtained.

 -- Statement 1, not in/top
Select top 50 * From pagetest 
 Where id not in (select top 9900 ID from pagetest order by ID) 
Order by ID 




 -- Statement 2, not exists
Select top 50 * From pagetest 
 Where not exists 
 (Select 1 from (select top 9900 ID from pagetest order by ID) A where a. ID = pagetest. ID) 
 Order by ID 

 -- Statement 3, max/top
Select top 50 * From pagetest 
 Where ID> (select max (ID) from (select top 9900 ID from pagetest order by ID)) 
 Order by ID 

-- Statement 4, row_number ()
Select top 50 * from 
 (Select row_number () over (order by ID) rownumber, * From pagetest) 
 Where rownumber> 9900 

 Select * from 
 (Select row_number () over (order by ID) rownumber, * From pagetest) 
 Where rownumber> 9900 and rownumber <9951 

 Select * from 
 (Select row_number () over (order by ID) rownumber, * From pagetest) 
Where rownumber between 9901 and 9950 

 -- Write 5: The row_number () variant shown in the post on csdn does not generate record numbers based on existing fields. Filter and sort records by conditions first, then a constant column in The result set is used to generate the record serial number.
Select * 
 From ( 
 Select row_number () over (order by tempcolumn) rownumber ,* 
 From (select top 9950 tempcolumn = 0, * From pagetest where 1 = 1 order by ID) 
 ) B 
 Where rownumber> 9900 

3. perform tests in the 10 thousand, 0.1 million (take 1990 pages), and 100 (take 19900 pages) records sets.

Test SQL:

Declare @ begin_date datetime
Declare @ end_date datetime
Select @ begin_date = getdate ()

<...... Your code ......>

Select @ end_date = getdate ()
Select datediff (MS, @ begin_date, @ end_date) as 'millisecond'
 

 

10 thousand: Basically there is no difference.

0.1 million:

1 million:

 

4. Conclusion:

1. Max/top and row_number () are all good paging methods. Compared with row_number (), it only supports sql2005 and later versions. Max/top has better portability and can be applied to both SQL2000 and access.

2. Not exists feels a little more efficient than not in.

3. The efficiency of writing three row_number () statements is similar.

4. The variation of row_number () is based on my test efficiency. Original post http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html here

 

PS. The above paging sorting is based on the ID of the auto-increment field. The test environment also provides int, nvarchar, and datetime fields. You can also try them. However, the sorting efficiency of large data volumes without indexes for non-primary keys should be unsatisfactory.

 

5. encapsulate rownumber and max/top into the stored procedure.

Rownumber ():

 Create proc [DBO]. [spsqlpagebyrownumber] 
@ Tbname varchar (255), -- table name
@ Tbfields varchar (1000), -- Return Field
@ Pagesize int, -- page size
@ Pageindex int, -- page number
@ Strwhere varchar (1000), -- Query Condition
@ Strorder varchar (255), -- sorting Condition
@ Total int output -- total number of returned records
As 
 Declare @ strsql varchar (5000) -- subject sentence
Declare @ strsqlcount nvarchar (500) -- query the total number of records.
 
 -------------- Total number of records ---------------
If @ strwhere! ='' 
 Begin 
 Set @ strsqlcount = 'select @ totalcout = count (*) from '+ @ tbname + 'where' + @ strwhere 
 End 
 Else 
 Begin 
 Set @ strsqlcount = 'select @ totalcout = count (*) from' + @ tbname 
 End 
 -------------- Pagination ------------
If @ pageindex <= 0 
 Begin 
 Set @ pageindex = 1 
 End 

 Set @ strsql = 'select * from (select row_number () over ('+ @ strorder +') rowid, '+ @ tbfields 
 + 'From' + @ tbname + 'where 1 = 1' + @ strwhere + ') TB where TB. rowid>' + STR (@ PageIndex-1) * @ pagesize) 
 + 'And TB. rowid <=' + STR (@ pageindex * @ pagesize) 

 Exec sp_executesql @ strsqlcount, n' @ totalcout int output', @ total output 
 Exec (@ strsql) 

Max/top: (if you write it down, the name of the primary key field must be "ID ")

 Create proc [DBO]. [spsqlpagebymaxtop] 
 @ Tbname varchar (255), -- table name
@ Tbfields varchar (1000), -- Return Field
@ Pagesize int, -- page size
@ Pageindex int, -- page number
@ Strwhere varchar (1000), -- Query Condition
@ Strorder varchar (255), -- sorting Condition
@ Total int output -- total number of returned records
As 
Declare @ strsql varchar (5000) -- subject sentence
Declare @ strsqlcount nvarchar (500) -- query the total number of records.
 
 -------------- Total number of records ---------------
If @ strwhere! ='' 
 Begin 
 Set @ strsqlcount = 'select @ totalcout = count (*) from '+ @ tbname + 'where' + @ strwhere 
 End 
 Else 
 Begin 
 Set @ strsqlcount = 'select @ totalcout = count (*) from' + @ tbname 
 End 
-------------- Pagination ------------
If @ pageindex <= 0 
 Begin 
 Set @ pageindex = 1 
 End 

 Set @ strsql = 'select top '+ STR (@ pagesize) +' * from' + @ tbname +'
Where ID> (select max (ID) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + 'id from' + @ tbname + ''+ @ strorder + '))
'+ @ Strorder +'' 

 Exec sp_executesql @ strsqlcount, n' @ totalcout int output', @ total output 
 Exec (@ strsql) 

Found in the garden max/top such a version, looks very powerful, http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html

Call:

 
Declare @ count int
-- Exec [DBO]. [spsqlpagebyrownumber] 'pagetest ',' * ', 50, 20,', 'order by id asc ', @ count output
Exec [DBO]. [spsqlpagebymaxtop] 'pagetest ',' * ', 50, 20,', 'order by id asc ', @ count output
Select @ count