Sidebar

When to use "TOP" vs "OFFSET...FETCH NEXT" to query MSSQL database.

0 votes
7.8K views
asked Jan 30, 2020 by rich-c-2789 (15,250 points)
edited Jan 31, 2020 by rich-c-2789
I am trying to write a dynamic query that may be used to page the result set however I hit this error:

The number of rows provided for a FETCH clause must be greater then zero.

USE CASE:
In my case if the number for the offset and number for the next rows are both zero I would rather it returned an empty result set.  Is this possible?

1 Answer

0 votes

First, some definitions from the resources listed at the end of this answer:

TOP: Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2019 (15.x). When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.  Applies to: All SQL Server and Azure SQL Databases.

OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero. Applies to: SQL Server 2012 (11.x) and later and Azure SQL Databases.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.  Applies to: SQL Server 2012 (11.x) and later and Azure SQL Databases.

Both TOP and OFFSET/FETCH are ways to limit the results returned.  NOTE: other database systems implement limiting results using other methods/keywords (ie: LIMIT - MySQL, ROWNUM - ORACLE).   

TOP can only return the first X records as opposed to OFFSET/FETCH. When OFFSET/FETCH is used together it can return a window of rows any where within the result set.

So, back to the question. Simple answer is if you just want to limit the the results to the first X records, TOP provides a quick succinct syntax. However, if you are on SQL Server 2012 or later OFFSET/FETCH provides more flexibility. Like returning the last 10 records.

To address the use case mentioned. You can use both TOP and OFFSET/FETCH to return results or an empty set. NOTE: this can only be done by switching between using TOP and OFFSET/FETCH since they can not be used together in the same query. If you try to use both in the same query you will get this error:

A TOP cannot be used in the same query or sub-query as an OFFSET.

Create your query dynamically to include one or the other as needed.

Example 1: When 50 is passed in for the OFFSET and 10 for the number of records to return output a query using OFFSET/FETCH.

SELECT [FirstName], [LastName], [PersonType] FROM [Person].[Person] ORDER BY [FirstName] OFFSET 50 ROWS FETCH NEXT 10 ROW ONLY

OUTPUT -- Returns 10 rows from the middle of the result set

Example 2: When any number is passed in for the OFFSET and 0 for the number of records to return output a query using TOP.  (This may not make sense to do this but let's not ask why.  If you want an empty set...)

SELECT TOP 0 [FirstName], [LastName], [PersonType] FROM [Person].[Person] ORDER BY [FirstName]

OUTPUT -- Returns an empty result set

emFor more details see the references below.

References:

https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

https://www.essentialsql.com/what-is-the-differenence-between-top-and-offset-fetch/

https://raresql.com/tag/the-number-of-rows-provided-for-a-fetch-clause-must-be-greater-then-zero/

https://www.w3schools.com/sql/sql_top.asp

answered Jan 30, 2020 by rich-c-2789 (15,250 points)
edited Jan 31, 2020 by rich-c-2789
...