Skip to content

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

High cognitive complexity in T-SQL queries makes code harder to understand and maintain, impacting developer productivity and reliability.

In T-SQL code, cognitive complexity arises when certain elements within SQL statements make them more difficult to read and understand. As queries grow more complex, they become harder to manage and maintain, increasing the risk of errors and reducing performance efficiency.

For example:

-- Example of a complex query
SELECT a.col1, b.col2
FROM TableA a
JOIN TableB b ON a.id = b.id
WHERE a.status = 'Active'
AND EXISTS (
SELECT 1
FROM TableC c
WHERE c.a_id = a.id
AND c.type IN (
SELECT type
FROM (
SELECT DISTINCT type FROM TableD
) AS SubQuery
)
);

This query is problematic due to several factors that contribute to its cognitive complexity, such as nested subqueries, multiple table joins, and the use of inline subqueries.

  • Complex join conditions with multiple tables increase mental effort for developers trying to understand the relationships and data flow.

  • Nesting and usage of subqueries, especially with constructs like EXISTS and IN , add layers of complexity, making it challenging to trace logical flow.

  • Logical conditions, such as those in WHERE , JOIN , and HAVING clauses, lead to intricate decision paths.

  • Common Table Expressions (CTEs) with recursion and nested CASE functions can further obfuscate the intended logic.

Managing cognitive complexity involves recognizing these elements and seeking ways to simplify the query structure, such as modularizing logical components or optimizing query logic. This helps in preventing performance bottlenecks and improving code maintainability.

This section provides strategies to reduce the cognitive complexity of T-SQL queries, aiming to improve their readability and maintainability.

Follow these steps to address the issue:

1.Identify complex structures in the query. Look for NESTED SUBQUERIES , excessive use of JOINS , and intricate WHERE conditions that make the query difficult to follow.

2.Extract complex subquery logic into Common Table Expressions (CTEs) . This approach breaks down complex logic into more understandable parts, enhancing readability and manageability.

3.Refactor JOIN conditions and where possible, reduce the number of joined tables. Simplify JOIN and WHERE clauses by consolidating logical expressions.

4.Use views to encapsulate frequent or complex query components, which can help abstract complexity away from the main query logic.

5.Avoid recursive CTEs unless necessary, as they can add to cognitive complexity. Use iterative methods if possible, or document the recursive logic clearly.

For example:

-- Refactored query using CTE for clarity
WITH TypeList AS (
SELECT DISTINCT type FROM TableD
),
FilteredData AS (
SELECT a.col1, b.col2, c.type
FROM TableA a
JOIN TableB b ON a.id = b.id
JOIN TableC c ON c.a_id = a.id
WHERE a.status = 'Active' AND c.type IN (SELECT type FROM TypeList)
)
SELECT col1, col2 FROM FilteredData;

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

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

The rule does not need Analysis Context or SQL Connection.

1 hour base effort, 20 minutes per issue.

Design Rules, Bugs

Cognitive Complexity

INSERT #TmpResult -- +1 target
SELECT doc.ID
, row.ItemId
, doc.CategoryId
, doc.DocumentDate
, row.Lot
, SUM(row.Quantity) AS Quantity
, row.Price
, SUM(row.TotalSum) AS TotalSum
, SUM(row.TotalVat) AS TotalVat
, SUM(COALESCE(-x.LinkQuantity,0)) AS Quantity -- +1 COALESCE
, SUM( -CONVERT(DECIMAL(24, 12), COALESCE(x.LinkQuantity,0) -- +1 COALESCE
* row.Price) ) AS TotalSum
, SUM( -CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12), COALESCE(x.LinkQuantity,0) -- +1 COALESCE
* row.Price)
* CONVERT(DECIMAL(24, 12), row.VatRate / 100.0)) ) AS TotalVat
FROM dbo.InventoryDocs -- +0 first table source
JOIN dbo.DocumentDetails row -- +1 join
ON doc.ID = row.DocId
AND doc.DocumentDate = row.DocumentDate -- +1 logical sequence
LEFT JOIN (SElECT x.SourceRowId -- +1 join, +2 select (nesting= +1)
, x.SourceDocumentDate
, SUM(x.LinkQuantity) As LinkQuantity
FROM dbo.InventoryRows x -- +0 first table source
WHERE x.LinkDocState = 2
GROUP BY x.SourceRowId -- +1 group by
, x.SourceDocumentDate
) AS x
ON row.ID = x.SourceRowId
AND row.DocumentDate = x.SourceDocumentDate -- +1 logical sequence
WHERE doc.DocumentDate BETWEEN getdate()-5 AND getdate()+5 -- +1 logical sequence
AND row.DocumentDate BETWEEN getdate()-5 AND getdate()+5
AND doc.State = 2
AND ((doc.DocTypeId IN (1,2,3))
OR (doc.DocTypeId IN (5, 6))) -- +1 logical sequence
AND doc.CategoryId IN (SELECT ID FROM #TmpCategory) -- +2 select (nesting= +1) , +0 first table source
AND row.ItemId IN (SELECT ID FROM #TmpItm) -- +2 select (nesting= +1) , +0 first table source
GROUP BY doc.ID -- +1 group by
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID -- +1 window function
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID -- +1 window function
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5 -- +1 logical sequence
ORDER BY TerritoryID,SalesYear;
  Message Line Column
1 SA0147 : The statement complexity is 2 points above the allowed 15. 1 0

Analysis Rules