SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax
Introduction
Section titled “Introduction”Cursors declared with the ANSI/ISO SQL 92 syntax can be inefficient for advanced uses in SQL Server.
Description
Section titled “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:
-- Declaration using ANSI/ISO SQL 92 syntaxDECLARE cursor_name CURSOR FORSELECT 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
Section titled “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:
-- Declaration using extended T-SQL syntax with more optionsDECLARE cursor_name CURSOR LOCAL FAST_FORWARD FORSELECT column_name FROM table_name;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”13 minutes per issue.
Categories
Section titled “Categories”Design Rules, Code Smells
Additional Information
Section titled “Additional Information”Example Test SQL
Section titled “Example Test SQL”DECLARE vend_cursor1 SCROLL CURSOR FOR SELECT * FROM Purchasing.VendorDECLARE vend_cursor2 CURSOR FOR SELECT * FROM Purchasing.Vendor FOR READ ONLY;DECLARE vend_cursor3 INSENSITIVE CURSOR FOR SELECT * FROM Purchasing.VendorDECLARE vend_cursor4 INSENSITIVE SCROLL CURSOR FOR SELECT * FROM Purchasing.VendorDECLARE vend_cursor5 CURSOR FOR SELECT VendorID, Name, LastName FROM Purchasing.Vendor FOR UPDATE OF Name, LastNameAnalysis Results
Section titled “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 |