Skip to content

SEM005 : Cognitive complexity

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.

The rule is only informational.

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

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

The rule does not need Analysis Context or SQL Connection.

Not configured.

Metrics

Cognitive Complexity

SELECT col1,col2,col3 -- +0
FROM table1
SELECT col1,col2,col3
FROM table1 t1 -- +1
INNER JOIN -- +1
(SELECT id FROM table2) t2 -- +1
ON t1.table2_id=t2.id
SELECT col1,col2,col3 -- +0
FROM table1
UNION -- +1
SELECT col1,col2,col3 -- +0
FROM table2
SELECT col1,col2,col3 -- +0
FROM table1
WHERE col1=1 AND col2=2 OR col3='a' -- +1
IF(datename(weekday,getdate()) IN (N'Saturday',N'Sunday')) -- +1
PRINT 'Weekend';
ELSE
BEGIN
GOTO Weekday; -- +1
END
Weekday:
PRINT 'Weekday'
  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

Analysis Rules