SEM005 : Cognitive complexity
Introduction
Section titled “Introduction”Description
Section titled “Description”Metrics rule - The rule calculates the cognitive complexity of statements. The rule gives a way to measure the complexity of a single SQL statement based on the existence of specific elements in it. The following elements in the SELECT, INSERT, UPDATE, DELETE, and MERGE statement can be considered when the query complexity is calculated: - The joined table sources - The target table in INSERT, UPDATE, DELETE and MERGE statements - Usages of CASE function - Usages of window functions - Usages of table hints, query hints and join hints - Existence of GROUP BY clause - Existence of ORDER BY clause - Existence of HAVING clause - Existence of COMPUTE or COMPUTE BY clause - Each UNION, INTERSECT, and EXCEPT clauses - Common Table Expression - Common Table Expression - Recursion - Logical expression sequences in WHERE, JOIN and HAVING clauses There are a few other statements, which have the cognitive complexity calculated - TRY-CATCH, IF-ELSE, WHILE, BREAK, CONTINUE, and GOTO statements. The nesting of some of the elements and statements also affects the readability of the code and thus increases the cognitive complexity: - Nesting of CASE functions and SELECT queries - Nesting of IF-ELSE, WHILE and TRY-CATCH statements. The complexity for each of the above elements can be changed using the rule parameters.
How to fix
Section titled “How to fix”The rule is only informational.
The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| JoinComplexity | The complexity value assigned for each joined table in the statement. | 1 |
| HintComplexity | The complexity value assigned for each hint found in the statement. | 1 |
| GroupByComplexity | The complexity value assigned for the GROUP BY clause. | 1 |
| OrderByComplexity | The complexity value assigned for the ORDER BY clause. | 0 |
| HavingComplexity | The complexity value assigned for the HAVING clause. | 1 |
| ComputeComplexity | The complexity value assigned for the COMPUTE and COMPUTE BY clauses. | 1 |
| UnionComplexity | The complexity value assigned for each usage of UNION, INTERSECT, and EXCEPT clauses inside a SELECT statement. | 1 |
| CteComplexity | The complexity value assigned for each Common Table Expression. | 0 |
| LogicalOpeartorSequenceComplexity | The complexity value assigned for each sequence of logical operators. | 1 |
| TargetComplexity | The complexity value assigned for the target table of a SELECT INTO, INSERT, UPDATE, DELETE, and MERGE statements | 0 |
| CteRecursionComplexity | The complexity value assigned for recursive common-table expressions. | 1 |
| WindowFunctionComplexity | The complexity value assigned for usages of windows functions. | 1 |
| NestingOfQueryComplexity | The complexity value added for each level of nesting of SQL query or CASE expression. | 1 |
| CoalesceExpressionComplexity | The complexity value assigned for usages of COLAESCE expressions. | 1 |
| CaseExpressionComplexity | The complexity value assigned for usages of searched and simple CASE expressions. | 1 |
| WhileStatementComplexity | The complexity value assigned for a WHILE statement. | 1 |
| IfElseComplexity | The complexity value assigned for an IF-ELSE statement. | 1 |
| TryCatchComplexity | The complexity value assigned for a TRY-CATCH statement. | 1 |
| GotoComplexity | The complexity value assigned for a GOTO statement. | 1 |
| NestingOfFlowBreakStatementComplexity | The complexity value added for each level of nesting of IF-ELSE, TRY-CATCH and WHILE statements. | 1 |
| WhileBreakAndContinueStatementComplexity | The complexity value assigned for BREAK and CONTINUE statements that are used inside a WHILE statement block. | 1 |
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”Not configured.
Categories
Section titled “Categories”Metrics
Additional Information
Section titled “Additional Information”Example Test SQL
Section titled “Example Test SQL”SELECT col1,col2,col3 -- +0FROM table1
SELECT col1,col2,col3FROM table1 t1 -- +1 INNER JOIN -- +1 (SELECT id FROM table2) t2 -- +1 ON t1.table2_id=t2.id
SELECT col1,col2,col3 -- +0FROM table1UNION -- +1SELECT col1,col2,col3 -- +0FROM table2
SELECT col1,col2,col3 -- +0FROM table1WHERE col1=1 AND col2=2 OR col3='a' -- +1
IF(datename(weekday,getdate()) IN (N'Saturday',N'Sunday')) -- +1 PRINT 'Weekend';ELSEBEGIN GOTO Weekday; -- +1END
Weekday:
PRINT 'Weekday'Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SEM005 : StatementComplexity=1 | 20 | 0 |
| 2 | SEM005 : StatementComplexity=1 | 24 | 4 |
| 3 | SEM005 : StatementComplexity=0 | 1 | 0 |
| 4 | SEM005 : StatementComplexity=1 | 4 | 0 |
| 5 | SEM005 : StatementComplexity=1 | 10 | 0 |
| 6 | SEM005 : StatementComplexity=2 | 16 | 0 |