Liwu_items tables, createtime columns to establish a clustered index
First, sqlserver2005-specific page syntax
Copy Code code as follows:
DECLARE @page int
DECLARE @pagesize int
Set @page = 2
Set @pagesize = 12
SET STATISTICS IO on
SELECT a.* from (
SELECT row_number () over (order by B.createtime DESC) as [Row_number], b.*
from [dbo]. [Liwu_items] As B) as a
WHERE A.[row_number] BETWEEN @pagesize + 1 and (@page * @pagesize)
ORDER BY A.[row_number]
Results:
(12 rows affected) Table ' Liwu_items '. Scan count 1, logical read 7 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Logical reading is 7 times
Execution Plan:
The main cost is scanned at the clustered index.
The second one is to implement pagination by using two top-order and reverse-order, and a different subquery for each page.
Copy Code code as follows:
DECLARE @page int
DECLARE @pagesize int
Set @page = 2
Set @pagesize = 12
SET STATISTICS IO on
SELECT * FROM (
Select Top (@pagesize) * FROM
(select Top (@page * @pagesize) * createtime desc) liwu_items
ORDER BY createtime ASC) b
ORDER BY createtime Desc
Results
(12 rows affected) Table ' Liwu_items '. Scan count 1, logical read 7 times, physical read 0 times, read 317 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Execution plan
The execution plan is similar to the first, but two of the resources are quite a bit.
The third, one of the most rubbish, is implemented in the NOT in clause, as follows
Copy Code code as follows:
DECLARE @page int
DECLARE @pagesize int
Set @page = 2
Set @pagesize = 12
SET STATISTICS IO on
Select Top (@pagesize) * from Liwu_items
where ItemId not in (
Select Top ((@page-1) * @pagesize) ItemId from Liwu_items ORDER BY createtime Desc)
ORDER BY Createtime Desc
Results
(12 rows affected) Table ' worktable '. Scan count 1, logical read 70 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' Liwu_items '. Scan count 2, logical read 18 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
The worst performance, two table processing, logic reading is very high, sweat.
Execution plan
This execution plan is not understood, and nested loops and table spooling take up a lot of resources.
Summary: The second paging method is nearly as efficient as the first paging method, but the second one can be used for older versions of SQL Server and even access, the last category.
