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?

How to Skip Top N Rows in SQL Server? - Interview Question of the Week #237 skippingrows-800x168

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.

Solarwinds

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.

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)

Solarwinds
, , , ,
Previous Post
How to Write in Text File from T-SQL in SQL Server? – Interview Question of the Week #236
Next Post
How to Create a Table Automatically in All Newly Created Databases? – Interview Question of the Week #238

Related Posts

1 Comment. Leave new

  • Hi Pinal, OFFSET and FETCH are a good solution but just for small number of records. When you are working with a lot off records the performance decrease when the @PageNumber is higher than 100.
    In this case, how can I solve the problem related to slow query answer whan I try to go into @pagenumbers up to 100?
    Best regards

    Reply

Leave a Reply

Menu