Lesson Learned #477:NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set
In the realm of SQL Server, certain combinations of commands and functions can lead to unexpected conflicts and errors. A notable example is the conflict between the NEXT VALUE FOR function and the ROWCOUNT setting. This article aims to dissect the nature of this error, explaining why it occurs, its implications, and how to effectively capture and analyze it using Extended Events in Azure SQL Database. For example, we got the following error message:Msg 11739, Level 15, State 1, Line 11 - NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. -NEXT VALUE FOR (Transact-SQL) - SQL Server | Microsoft Learn
