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

[Jose_Manuel_Jurado]

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!