SQL Server paging query about using the top method and Row_number () to parse functions differently _mssql

Near the Spring Festival, the heart has already flown away from work, the following small series for everyone to organize some of the database of several paging query.

SQL Sever before 2005 version:

Select Top Page Size * from
table name
where ID not in
(
select top Page Size * (Query page-1) ID from table name ORDER by ID
)

For example:

Select Top 10 *--10 for page size from
[tccline].[ DBO]. [Cline_commonimage]
Where ID
is--40:10* (5-1)-
-Page Size * (Query page-1)
select top IDs from [tccline].[ DBO]. [Cline_commonimage] ORDER by ID

The results are:

SQL Sever version 2005 and above, a number of paging query method:

* * FirstIndex: Start index
* pageSize: Number of Per-page display
* ordercolumn: Sorted field name
* SQL: Can be a simple single table query statement, It can also be a complex multiple table joint query statement
* *

For example:

Select Top numcomimg.* from 
(select Row_number ()-(Order by ID ASC) as rownumber,* from (SELECT * FROM [tccline ]. [dbo]. [Cline_commonimage]) As Comimg)

Results:

Are these two methods just one more column of Rewnumber? Of course not, look at the internal differences:

On two SQL, add the following SQL separately, and use MS's include execution plan to facilitate viewing execution details:

SET STATISTICS time on

SQL to execute:

SET STATISTICS time in
go
select top numcomimg.* from 
(select Row_number () over (order by ID ASC) as RowNum ber,* from (SELECT * FROM [tccline].[ DBO]. [Cline_commonimage])  As Comimg)
as numcomimg where rownumber>40
SET STATISTICS time in
go
select Top 10 *--10 for page size
from [Tccline]. [dbo]. [Cline_commonimage]
Where ID
is--40:10* (5-1)-
-Page Size * (Query page-1)
select top IDs from [tccline].[ DBO]. [Cline_commonimage] ORDER by ID
)

After execution, view the execution plan:

It can be seen that two of the same functions of SQL, the implementation of the use of row_number (), compared to the pure top method, the query cost much less, the above figure shows 28:72, pure top mode, using two times clustered scan.

Then look at the execution time information:

Row_number () Way of:

Pure Top mode:

In contrast, the row_number () parse function is more efficient than write.

The above is a small compilation for you to share SQL Server paging query about the use of the top method and row_number () parsing functions, I hope to help.