When retrieving data from a specific table in SQL Server, we often need to retrieve a specific sorted range and number of records instead of retrieving all the records. To achieve this, we can use either the TOP or ROW_NUMBER statements to limit the number of retrieved records. But which one is better? Let’s explore the differences and performance implications of using these two statements.
The TOP Statement
The TOP statement is used to limit the number of rows returned in the query result set to a specific number. If the TOP statement is used with the ORDER BY clause, the returned result will be limited to the first ordered N records; otherwise, it will return the first N records with no order.
The ROW_NUMBER Statement
The ROW_NUMBER statement returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. It allows us to retrieve a specific range of records by filtering on the row number.
Performance Comparison
To compare the performance of the TOP and ROW_NUMBER statements, we conducted several tests using the TIME and IO statistics and the Actual Execution Plan.
Test 1 – Return 10 Rows
In this test, we retrieved the first 10 records from the Product table using both the TOP and ROW_NUMBER statements. The results were the same, as both queries used the ORDER BY clause to sort the records.
Comparing the execution plans, we found that the query using the TOP statement had less weight (37%) compared to the query using the ROW_NUMBER statement (63%). The IO statistics also showed that the query using the TOP statement performed fewer logical reads.
The TIME statistics revealed that the query using the TOP statement executed faster than the query using the ROW_NUMBER statement.
Test 2 – Return 100 Rows
In this test, we increased the number of records to be returned to 100. Both the TOP and ROW_NUMBER statements performed equally in terms of execution plan weight and logical reads. However, the query using the TOP statement executed faster.
Test 3 – Return 1000 Rows
In this test, we further increased the number of records to be returned to 1000. Surprisingly, the query using the ROW_NUMBER statement had less weight in the execution plan compared to the query using the TOP statement. However, both queries performed the same number of logical reads, and the query with the TOP statement executed faster.
Conclusion
Based on our tests, it is clear that the TOP statement outperforms the ROW_NUMBER statement in terms of execution time. Although the execution plans may vary, the TOP statement consistently performs better in terms of time required to execute the query. Therefore, if you need to retrieve a specific number of records from a table, it is recommended to use the TOP statement.