SA0147 : The Cognitive Complexity of the statement should not be too high

The topic describes the SA0147 analysis rule.

Message

The Cognitive Complexity of the statement should not be too high

Description

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

Review the code that has higher than allowed complexity and try to refactor it and reduce the complexity value.

Scope

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

Parameters
Name Description Default Value
AllowedCognitiveComplexity

The maximum allowed value for the Cognitive Complexity.

15
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
1 hour base effort, 20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1INSERT                #TmpResult                                                  -- +1 target
 2SELECT                  doc.ID
 3                        , row.ItemId
 4                        , doc.CategoryId
 5                        , doc.DocumentDate
 6                        , row.Lot
 7                        , SUM(row.Quantity)                AS Quantity        
 8                        , row.Price
 9                        , SUM(row.TotalSum)                AS TotalSum
10                        , SUM(row.TotalVat)                AS TotalVat
11                        , SUM(COALESCE(-x.LinkQuantity,0))                AS Quantity         -- +1 COALESCE
12                        , SUM( -CONVERT(DECIMAL(24, 12), COALESCE(x.LinkQuantity,0) -- +1 COALESCE
13                                                * row.Price)        )                        AS TotalSum
14                        , SUM( -CONVERT(DECIMAL(24, 12),
15                                        CONVERT(DECIMAL(24, 12), COALESCE(x.LinkQuantity,0) -- +1 COALESCE
16                                                * row.Price)
17                                                * CONVERT(DECIMAL(24, 12), row.VatRate / 100.0))        )        AS TotalVat
18FROM                dbo.InventoryDocs                                           -- +0 first table source
19JOIN                dbo.DocumentDetails row                                     -- +1 join
20ON                        doc.ID = row.DocId
21                        AND doc.DocumentDate = row.DocumentDate                     -- +1 logical sequence
22LEFT JOIN        (SElECT                          x.SourceRowId                             -- +1 join, +2 select (nesting= +1) 
23                                                        , x.SourceDocumentDate
24                                                        , SUM(x.LinkQuantity) As LinkQuantity
25                        FROM                        dbo.InventoryRows x                         -- +0 first table source 
26                        WHERE                        x.LinkDocState = 2
27                        GROUP BY                  x.SourceRowId                             -- +1 group by
28                                                        , x.SourceDocumentDate
29                        ) AS x
30ON                        row.ID = x.SourceRowId                                      
31                        AND row.DocumentDate = x.SourceDocumentDate                 -- +1 logical sequence
32WHERE                doc.DocumentDate BETWEEN getdate()-5 AND getdate()+5        -- +1 logical sequence
33                        AND row.DocumentDate BETWEEN getdate()-5 AND getdate()+5
34                        AND doc.State = 2
35                        AND ((doc.DocTypeId IN (1,2,3)) 
36                                        OR (doc.DocTypeId IN (5, 6)))                       -- +1 logical sequence
37                        AND doc.CategoryId IN (SELECT ID FROM #TmpCategory)         -- +2 select (nesting= +1) , +0 first table source
38                        AND row.ItemId IN (SELECT ID FROM #TmpItm)                  -- +2 select (nesting= +1) , +0 first table source
39GROUP BY          doc.ID                                                    -- +1 group by
40
41SELECT BusinessEntityID, TerritoryID   
42   ,DATEPART(yy,ModifiedDate) AS SalesYear  
43   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
44   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID            -- +1 window function
45                                            ORDER BY DATEPART(yy,ModifiedDate)  
46                                           ),1) AS MovingAvg  
47   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID            -- +1 window function
48                                            ORDER BY DATEPART(yy,ModifiedDate)  
49                                            ),1) AS CumulativeTotal             
50FROM Sales.SalesPerson  
51WHERE TerritoryID IS NULL OR TerritoryID < 5                                    -- +1 logical sequence
52ORDER BY TerritoryID,SalesYear;
Analysis Results
  Message Line Column
1 SA0147 : The statement complexity is 2 points above the allowed 15. 1 0
See Also

Other Resources

Analysis Rules