How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237

Question: How to skip top N Rows in SQL Server and retrieve the rest of the rows?

Answer: I had asked the above question in one of the interviews last week and to my surprise, I have received a lot of solution with the help of Ranking Function, TempTables, CTEs, and subqueries but had received no answer with OFFSET and FETCH keywords of SQL Server. This was indeed very surprising as I personally believe OFFSET and FETCH are very efficient when we are dealing with sets of an ordered row from the table.

Let us see the following example which I have written with the help of a sample database AdventureWorks. In the following table, we can skip the top 100 rows by just using the keyword OFFSET and not specifying the FETCH keyword.

SELECT *
FROM [AdventureWorks2014].[Person].[Address]
ORDER BY AddressID
OFFSET 100 ROWS;

Yes, it is indeed very simple and if you compare the performance of this query with the other workaround queries you will notice that this query performs much faster than other queries. I have done similar tests before and always the result has been in the favor of the OFFSET and FETCH keywords.

Here are a few additional blog post related to the topic of retrieving N rows.

• SQL SERVER – Retrieving N Rows After Ordering Query With OFFSET
• SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server
• SQL SERVER – Row Offset in SQL Server For Different Version
• SQL SERVER – MySQL – LIMIT and OFFSET – Skip and Return Only Next Few Rows – Paging Solution
• SQL SERVER – Server-Side Paging in SQL Server – A Better Alternative
• How to do Pagination in SQL Server? – Interview Question of the Week #111

Please, let me know in the comments area if I have asked you this question in the interview, what would you have responded.

Reference: Pinal Dave (https://blog.sqlauthority.com)