SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax

Cursors declared with the ANSI/ISO SQL 92 syntax can be inefficient for advanced uses in SQL Server.

Description

Using ANSI/ISO SQL 92 syntax to declare cursors in T-SQL code may limit functionality and flexibility when managing cursor-related operations in SQL Server. Cursors, when not used carefully, can lead to performance issues, as they process row-by-row instead of set-based operations that SQL Server is optimized for.

For example:

SQL
Copy
1-- Declaration using ANSI/ISO SQL 92 syntax
2DECLARE cursor_name CURSOR FOR 
3SELECT column_name FROM table_name;

This approach lacks many of the advanced options offered by the extended T-SQL syntax, such as additional cursor behaviors, fetching options, and scope declarations, which can be crucial for complex database tasks.

  • Limited functionality due to lack of extended options in ANSI/ISO syntax.

  • Potential performance degradation due to row-by-row processing when alternative set-based operations could be utilized.

How to fix

Rewrite the cursor declaration using the extended T-SQL syntax to improve functionality and performance.

Follow these steps to address the issue:

  1. Replace the ANSI/ISO SQL 92 syntax with T-SQL syntax to declare cursors, offering more options and flexibility. Use DECLARE CURSOR with T-SQL’s extended options.

  2. Specify any additional behaviors, such as LOCAL or GLOBAL to control the cursor’s scope and lifespan as needed.

  3. Consider using FAST_FORWARD, SCROLL, or other options to optimize performance based on your use case.

  4. Implement set-based operations instead of cursors where possible to enhance performance.

For example:

SQL
Copy
1-- Declaration using extended T-SQL syntax with more options
2DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
3SELECT column_name FROM table_name;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information
Example Test SQL
SQL
Copy
1DECLARE vend_cursor1 SCROLL CURSOR FOR SELECT * FROM Purchasing.Vendor
2DECLARE vend_cursor2 CURSOR FOR SELECT * FROM Purchasing.Vendor  FOR READ ONLY;
3DECLARE vend_cursor3 INSENSITIVE CURSOR FOR SELECT * FROM Purchasing.Vendor
4DECLARE vend_cursor4 INSENSITIVE SCROLL CURSOR FOR SELECT * FROM Purchasing.Vendor 
5DECLARE vend_cursor5 CURSOR FOR SELECT VendorID, Name, LastName FROM Purchasing.Vendor FOR UPDATE OF Name, LastName
Analysis Results
  Message Line Column
1 SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax. 1 8
2 SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax. 2 8
3 SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax. 3 8
4 SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax. 4 8
See Also

Other Resources

Analysis Rules