For SQL Server paging query, the Top method and row_number () parsing function are different.

For SQL Server paging query, the Top method and row_number () parsing function are different.

The Spring Festival is approaching, and the heart is no longer at work. The following small series will help you sort out several database paging queries.

SQL Server versions earlier than Sever 2005:

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) order by id

For example:

Select top 10 * -- 10 is the page size from [TCCLine]. [dbo]. [CLine_CommonImage] where id not in (-- 40 is calculated as follows: 10 * (5-1) -- page size * (query page-1) select top 40 id from [TCCLine]. [dbo]. [CLine_CommonImage] order by id) order by id

Result:

SQL Sever 2005 and later versions have multiple paging query methods:

/** FirstIndex: Start Index * pageSize: number displayed per page * orderColumn: name of the sorted field * SQL: it can be a simple single table query statement, it can also be a complex multi-table joint query statement */select top pageSize o. * from (select row_number () over (order by orderColumn) as rownumber, * from (SQL) as o where rownumber> firstIndex;

For example:

select top 10 numComImg.* from ( select row_number() over(order by id asc) as rownumber,* from (select * FROM [TCCLine].[dbo].[CLine_CommonImage]) as comImg)as numComImg where rownumber>40 

Result:

Are the two methods simply adding a rewnumber column? Of course not. Let's look at the internal differences:

Add the following SQL statements to the two SQL statements and use "including execution plans" of MS to view execution details:

SET STATISTICS TIME ONGO 

SQL to be executed:

Set statistics time ONGOselect top 10 numComImg. * from (select row_number () over (order by id asc) as rownumber, * from (select * FROM [TCCLine]. [dbo]. [CLine_CommonImage]) as comImg) as numComImg where rownumber> 40 set statistics time ONGOselect top 10 * -- 10 is the page size from [TCCLine]. [dbo]. [CLine_CommonImage] where id not in (-- 40 is calculated as follows: 10 * (5-1) -- page size * (query page-1) select top 40 id from [TCCLine]. [dbo]. [CLine_CommonImage] order by id) order by id

After execution, view the execution plan:

It can be seen that when executing two SQL statements with the same function, row_number () is much lower than the pure TOP method, and the query overhead is shown in the 28: 72, pure top mode, two clustering scans are used.

Let's take a look at the execution time information:

Row_number:

Pure top mode:

In contrast, the row_number () parsing function is more efficient to write.

The above is a small part of the SQLSERVER paging query for everyone about the use of Top method and row_number () Resolution function is different, I hope to help you.

Articles you may be interested in:

• Summary of usage of the SQL Server sort functions ROW_NUMBER and RANK
• SQL adds the number (ROW_NUMBER) to the query result and merges multiple query results.
• Usage of ROW_NUMBER, RANK, and DENSE_RANK in SQLSERVER 2005
• Php uses the select statement to implement mysql paging Query
• Sqlserver uses row_number and partition by grouping to retrieve top data
• Stored Procedure Code for three types of SQL queries by PAGE
• Mysql paging principle and efficient mysql paging query statements
• Two types of SQL paging query statements
• Several methods of querying SQL by PAGE