Hi everybody.
At our organization, we recently bought SonarQube Enterprise Edition. Now we have version 8.5.1 installed. We had notice that the TSQL plugin is lacking one very important rule that we have to enforce: to warn when a CURSOR is created. Please, SonarQube team, can you help us with this rule?
description of the Rule: A warning must be issued on CURSOR creation. As everybody knows and is well documented, creation of SQL cursors should be avoided and only used when it’s impossible to find a more efficient way to solve a problem. Must cursors can and should be replaced using Common Table Expresions, temporary tables, joins, WHILE statements or even a single SELECT statement. SQL cursors have a extremely poor performance and should be avoided at all in large databases. At our organization (a bank), the use of cursors is forbidden by DBAs.
-
snippet of Noncompliant Code
-
snippet of Compliant Code (fixing the above noncompliant code)
There are many ways to replace a cursor, it depends on what the cursor does. Here are some examples: https://stackoverflow.com/questions/5425642/what-is-an-alternative-to-cursors-for-sql-looping -
exceptions to the Noncompliant Code, i.e. conditions in which the noncompliant code should not raise an issue so that we reduce the number of False Positives.
In general, the use of cursors should be avoided at all when dealing with large tables. In the case of small tables, there are no major performance drawbacks, but more efficient solutions often exist.
-
external references and/or language specifications
The use of TSQL cursors is explained here:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15-
Examples of real open source projects that have been impacted by this issue and fixed it (eg: link to the commit fix / CVEs for security rules / etc)
There are many performance comparisons between using cursors and their alternatives available on the web. We works with proprietary code at our organization. -
Documentation/Blog post explaining the issue and what should be done instead.
Here are some posts about the issue:
https://www.sqlservercentral.com/articles/replacing-cursors-and-while-loops
https://www.sqlshack.com/understanding-cursors-replacing-joins-sql-server/
https://www.c-sharpcorner.com/article/cursors-in-sql-server/
-
-
type : Code Smell
-
Tags: tsql, sonarqube
Thanks a lot for your attention.
Best regards,
Jorge