Lesson Learned #477:NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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

Section 1: Understanding the Error

What is NEXT VALUE FOR? The NEXT VALUE FOR function in SQL Server is a crucial tool for generating sequential values from a defined sequence. It's commonly used for auto-generating unique identifiers, like primary keys.
Conflict with ROWCOUNT: The error arises when NEXT VALUE FOR is used in conjunction with the ROWCOUNT option. ROWCOUNT, when set, limits the number of rows affected by a query. However, NEXT VALUE FOR expects to operate without such limitations, leading to a conflict. This issue can also manifest when using TOP or OFFSET clauses, which similarly restrict the result set.
Error Scenario: Imagine a scenario where a developer attempts to retrieve the next value from a sequence while ROWCOUNT is set to a specific limit. This operation triggers an error, as SQL Server cannot reconcile the sequence's need for unbounded operation with the imposed row count restriction.

Section 2: Capturing the Error with Extended Events

Introduction to Extended Events: Extended Events are a lightweight, highly configurable system for monitoring and troubleshooting in SQL Server and Azure SQL Database.
Setting up an Extended Event Session: Guide the reader through setting up an Extended Event session to capture this specific error. Mention the need to focus on the error_reported event and how to configure the session to target the ring buffer for data collection.
Querying the Ring Buffer: Provide a detailed explanation and a sample query on how to retrieve and analyze the error information from the ring buffer. This will help in understanding the occurrence and frequency of the error in a live environment.

You could reproduce the issue following this syntax:

CREATE SEQUENCE TestSequence AS INT START WITH 1 INCREMENT BY 1; SET ROWCOUNT 1; SELECT NEXT VALUE FOR TestSequence, * FROM MyTable;

In order to capture this info we could create an extended event

CREATE EVENT SESSION [CaptureError] ON database ADD EVENT sqlserver.error_reported( ACTION(sqlserver.sql_text) ) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO ALTER EVENT SESSION [CaptureError] ON database STATE = START; SELECT event_data.value('(@timestamp)[1]', 'DATETIME2') AS TimeStamp, event_data.value('(data[@name="error_number"]/value)[1]', 'INT') AS ErrorNumber, event_data.value('(data[@name="message"]/value)[1]', 'VARCHAR(MAX)') AS ErrorMessage, event_data.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS SqlText FROM ( SELECT CAST(target_data AS XML) AS target_data FROM sys.dm_xe_database_session_targets AS t INNER JOIN sys.dm_xe_database_sessions AS s ON t.event_session_address = s.address WHERE s.name = 'CaptureError' AND t.target_name = 'ring_buffer' ) AS tab CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(event_data)

Enjoy!

Leave a Reply Cancel reply