4

I have found a difference in behavior on a piece of code between database compatibility levels and wondered what the cause of this is. Below is a simple example that iterates through a tally table and changes the ROWCOUNT option on the 50th iteration:

Setup:

/* Create tally table */

SELECT  TOP 100
        ROW_NUMBER() OVER (ORDER BY a.object_id) AS Number
INTO    #Tally
FROM    sys.objects a
        CROSS JOIN sys.objects b;

/* Create Some databases with different compatibility levels */

CREATE DATABASE [100Compat] WITH COMPATIBILITY_LEVEL = 100
CREATE DATABASE [110Compat] WITH COMPATIBILITY_LEVEL = 110
CREATE DATABASE [120Compat] WITH COMPATIBILITY_LEVEL = 120
CREATE DATABASE [130Compat] WITH COMPATIBILITY_LEVEL = 130

The code that is affected:

/* cursor through the tally table */
DECLARE MyCursor CURSOR
FOR
SELECT  Number
FROM    #Tally
FOR READ ONLY;

DECLARE @num INT;

OPEN MyCursor

FETCH MyCursor
INTO @num
WHILE @@FETCH_STATUS = 0

BEGIN
    SET ROWCOUNT 0

    /* change the value for ROWCOUNT on iteration 50 */
    IF @num = 50 SET ROWCOUNT 1

    PRINT @num

    FETCH MyCursor
    INTO @num
END

SET ROWCOUNT 0

CLOSE MyCursor
DEALLOCATE MyCursor

If I run the above against [100Compat] it prints the numbers 1-100 in the messages window.

If I run the above against [110Compat],[120Compat] or [130Compat] I can see the values 1-50 in the messages window and the error message

Msg 16958, Level 16, State 3, Line 41 Could not complete cursor operation because the set options have changed since the cursor was declared.

what is the difference between the two compatibility modes that causes this change in behavior? Is there a trace flag that was enabled / disabled in 110 or something similar?

I have looked at this article but nothing seems obvious as to what causes the difference

8

SQL Server 2012 introduced a breaking change to SET ROWCOUNT:

The behavior of SET ROWCOUNT was changed to improve the efficiency of many common queries and can affect the query plans for compatibility levels 110 and higher. An error can result when the SET ROWCOUNT statement changes the ROWCOUNT value to 0 or from 0 after a DECLARE CURSOR statement and before a FETCH statement. To avoid this error, set the database compatibility level to 100 or set the ROWCOUNT outside of the cursor statements.

This change is gated by the database compatibility level as your repro script demonstrates. Compatibility level 110 (SQL 2012) or higher will result in an error when the ROWCOUNT setting is changed after the cursor is declared.

2
  • Thanks, I knew the documentation would be out there somewhere!
    – SEarle1986
    Nov 3 '21 at 12:02
  • @SEarle1986, the older doc is hard to find; had to exercise by bingle foo to dig it up.
    – Dan Guzman
    Nov 3 '21 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.