SEM005 : Cognitive complexity

The topic describes the SEM005 analysis rule.

Message

Cognitive complexity

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

The rule is only informational.

Scope

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

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

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
Not configured.
Categories

Metrics

Additional Information
Example Test SQL
SQL
Copy
 1SELECT col1,col2,col3                                -- +0
 2FROM table1
 3
 4SELECT col1,col2,col3
 5FROM table1 t1                                                -- +1
 6     INNER JOIN                                                -- +1 
 7         (SELECT id FROM table2) t2                -- +1
 8         ON t1.table2_id=t2.id                        
 9
10SELECT col1,col2,col3                                -- +0
11FROM table1
12UNION                                                                -- +1
13SELECT col1,col2,col3                                -- +0
14FROM table2
15
16SELECT col1,col2,col3                                -- +0
17FROM table1
18WHERE col1=1 AND col2=2 OR col3='a' -- +1
19
20IF(datename(weekday,getdate()) IN (N'Saturday',N'Sunday')) -- +1
21        PRINT 'Weekend';
22ELSE
23BEGIN
24    GOTO Weekday; -- +1
25END
26
27Weekday:
28
29PRINT 'Weekday'
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
See Also

Other Resources

Analysis Rules