In this blog post, let’s learn about the error message “127 – A TOP N or FETCH rowcount value may not be negative.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
127 – A TOP N or FETCH rowcount value may not be negative.
Reason for the Error
The SQL Server Error Msg 127 – A TOP N or FETCH rowcount value may not be negative, occurs when you try to use a negative value in the TOP N or FETCH clause of a SQL query.
The TOP N and FETCH clauses are used to limit the number of rows returned by a query. For example, consider the following query:
SELECT TOP 10 column1, column2 FROM table1;
In this query, the TOP clause limits the result set to the first 10 rows returned by the query. Similarly, the FETCH clause can be used to limit the result set to a specific range of rows, as shown in the following query:
SELECT column1, column2 FROM table1 ORDER BY column1 OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
In this query, the FETCH clause limits the result set to the 10 rows starting from the 6th row returned by the query.
However, if you try to use a negative value in the TOP N or FETCH clause, SQL Server will return the error message “A TOP N or FETCH rowcount value may not be negative.”
For example, consider the following query in the AdventureWorks table
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (-1000) [DepartmentID]
,[Name]
,[GroupName]
,[ModifiedDate]
FROM [AdventureWorks2019].[HumanResources].[Department]In this query, the TOP clause specifies a negative value (-1000), which is not allowed and will trigger the error message.
Solution
To fix the issue, you need to use a positive value in the TOP N or FETCH clause. Alternatively, you can remove the clause altogether if you don’t want to limit the result set.
For example, you can modify the above query as follows:
In this query, the TOP clause specifies a positive value (1000) and will limit the result set to the first 1000 rows returned by the query.
