Changing SET options within cursor – Different behavior based on compatibility level

compatibility-levelcursorssql-server-2008sql-server-2016

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

Best Answer

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.

Related Solutions

Sql-server – the actual behavior of compatibility level 80

From the docs:

Sets certain database behaviors to be compatible with the specified version of SQL Server.
...
Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

In my interpretation, compatibility mode is about behavior and parsing of syntax, not for things like the parser saying, "Hey, you can't use ROW_NUMBER()!" Sometimes the lower compatibility level allows you to continue getting away with syntax no longer supported, and sometimes it prevents you from using new syntax constructs. The documentation lists several explicit examples, but here are a few demonstrations:

Passing built-in functions as function arguments

This code works in compatibility level 90+:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

But in 80 it yields:

Msg 102, Level 15, State 1
Incorrect syntax near '('.

The specific problem here is that in 80 you aren't allowed to pass a built-in function into a function. If you want to stay in 80 compatibility mode, you can work around this by saying:

DECLARE @db_id INT = DB_ID();

SELECT * 
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

Passing a table type to a table-valued function

Similar to the above, you can get a syntax error when using a TVP and trying to pass it to a table-valued function. This works in modern compat levels:

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS 
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

However, change the compatibility level to 80, and run the last three lines again; you get this error message:

Msg 137, Level 16, State 1, Line 19
Must declare the scalar variable "@foo".

Not really any good workaround off the top of my head, other than upgrading the compat level or getting the results a different way.

Using qualified column names in APPLY

In 90 compatibility mode and up, you can do this without problem:

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:

Msg 102, Level 15, State 1
Incorrect syntax near '.'.

ORDER BY an alias that happens to match a column name

Consider this query:

SELECT name = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.name;

In 80 compatibility mode, the results are as follows:

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

In 90 compatibility mode, the results are quite different:

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

The reason? In 80 compatibility mode, the table prefix is ignored entirely, so it is ordering by the expression defined by the alias in the SELECT list. In newer compatibility levels, the table prefix is considered, so SQL Server will actually use that column in the table (if it is found). If the ORDER BY alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:

SELECT myname = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.myname;

The result is ordered by the myname expression in 80, because again the table prefix is ignored, but in 90 it generates this error message:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'myname'.

This is all explained as well in the documentation:

When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.

For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.

ORDER BY something not in the SELECT list

In 90 compatibility mode you can't do this:

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

Result:

Msg 104, Level 16, State 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

In 80, though, you can still use this syntax.

Old, icky outer joins

80 mode also allows you to use the old, deprecated outer join syntax (*=/=*):

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:

Msg 4147, Level 15, State 1
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

In SQL Server 2012, this is no longer valid syntax at all, and yields the following:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*='.

Of course in SQL Server 2012 you can no longer work around this problem using compatibility level, since 80 is no longer supported. If you upgrade a database in 80 compat mode (by in-place upgrade, detach/attach, backup/restore, log shipping, mirroring, etc.) it will automatically be upgraded to 90 for you.

Table hints without WITH

In 80 compat mode, you can use the following and the table hint will be observed:

SELECT * FROM dbo.whatever NOLOCK;

In 90+, that NOLOCK is no longer a table hint, it's an alias. Otherwise, this would work:

SELECT * FROM dbo.whatever AS w NOLOCK;

But it doesn't:

Msg 1018, Level 15, State 1
Incorrect syntax near 'NOLOCK'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Now, to prove that the behavior is not observed in the first example when in 90 compat mode, use AdventureWorks (making sure it's in a higher compat level) and run the following:

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks 
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

This one is particularly problematic because the behavior changes without an error message or even an error. And it's also something that the upgrade advisor and other tools might not even spot, since for all it knows, that's a table alias.

Conversions involving new date/time types

The new date/time types introduced in SQL Server 2008 (e.g. date and datetime2) support a much larger range than the original datetime and smalldatetime). Explicit conversions of values outside the supported range will fail no matter what the compatibility level, for example:

SELECT CONVERT(SMALLDATETIME, '00010101');

Yields:

Msg 242, Level 16, State 3
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

However, implicit conversions will work themselves out in the newer compatibility levels. For example this will work in 100+:

SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');

But in 80 (and also in 90), it yields a similar error as above:

Msg 242, Level 16, State 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Redundant FOR clauses in triggers

This is an obscure scenario that came up here. In 80 compatibility mode, this will succeed:

CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;

In 90 compatibility and higher, this no longer parses, and instead you get the following error message:

Msg 1034, Level 15, State 1, Procedure tx
Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

PIVOT/UNPIVOT

Some forms of syntax won't work under 80 (but work just fine in 90+):

SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;

This yields:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'for'.

For some workarounds, including CROSS APPLY, see these answers.

New built-in functions

Try using new functions like TRY_CONVERT() in a database with compatibility level < 110. They are simply not recognized there at all.

SELECT TRY_CONVERT(INT, 1);

Result:

Msg 195, Level 15, State 10
'TRY_CONVERT' is not a recognized built-in function name.

Recommendation

Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.

Sql-server – Cursors and concurrent users

So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.

This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.

Best Answer

Related Solutions

Sql-server – the actual behavior of compatibility level 80

Sql-server – Cursors and concurrent users

Related Question