SQL paging query, pure top mode and row_number () use and difference of analytic functions

Listen to colleagues to share several database paging query, I feel, or need to tidy up the MS Sqlsever paged query.

Previous versions of SQL Sever 2005:

Select Top *  from Table name where  not inch  Selecttop page Size *(Query page 1fromorder   by ID)order by ID

For example:

Select Top Ten * --10 for page size from [Tccline].[dbo].[Cline_commonimage]whereId not inch( --40 is calculated as: 10* (5-1) --Page Size * (Query page-1) Select Top  +Id from [Tccline].[dbo].[Cline_commonimage] Order  byID)Order  byId

The result is:

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

/* * FirstIndex: Start index * pageSize: Number per page * ordercolumn: Sorted field name * SQL: Can be a simple single-table query statement or a complex multi-table union query statement */ Select top pageSize o.* from (selectover (orderby as RowNumber,*from aswhere rownumber>firstindex;

For example:

Select Top TenNumcomimg.*  from ( SelectRow_number () Over(Order  byIdASC) asRowNumber*  from(Select *   from [Tccline].[dbo].[Cline_commonimage]) ascomimg) asNumcomimgwhereRowNumber> +

Results:

These two methods, is just a list of rewnumber? Of course not, look at the internal differences:

On two SQL, add the following SQL, and use MS's "include execution plan" to see execution Details:

SET STATISTICS  on GO

SQL to execute:

SET STATISTICSTime onGOSelect Top TenNumcomimg.*  from ( SelectRow_number () Over(Order  byIdASC) asRowNumber*  from(Select *   from [Tccline].[dbo].[Cline_commonimage]) ascomimg) asNumcomimgwhereRowNumber> +SET STATISTICSTime onGOSelect Top Ten * --10 for page size from [Tccline].[dbo].[Cline_commonimage]whereId not inch( --40 is calculated as: 10* (5-1) --Page Size * (Query page-1) Select Top  +Id from [Tccline].[dbo].[Cline_commonimage] Order  byID)Order  byId

After execution, review the execution plan:

It can be seen that two of the same functions of SQL, when executed, use Row_number (), compared to the pure top method, the query cost is much less, display 28:72, pure top mode, using two times the aggregation scan.

Then take a look at the execution time information:

Row_number () Mode:

Pure Top mode:

In contrast, the row_number () analytic function is more efficient than writing.

SQL paging query, pure top mode and row_number () use and difference of analytic functions