SA0147 : The Cognitive Complexity of the statement should not be too high
Introduction
Section titled “Introduction”High cognitive complexity in T-SQL queries makes code harder to understand and maintain, impacting developer productivity and reliability.
Description
Section titled “Description”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 querySELECT a.col1, b.col2FROM TableA aJOIN TableB b ON a.id = b.idWHERE 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
EXISTSandIN, add layers of complexity, making it challenging to trace logical flow. -
Logical conditions, such as those in
WHERE,JOIN, andHAVINGclauses, 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.
How to fix
Section titled “How to fix”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 clarityWITH 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.
Parameters
Section titled “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
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour base effort, 20 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”Example Test SQL
Section titled “Example Test SQL”INSERT #TmpResult -- +1 targetSELECT 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 TotalVatFROM dbo.InventoryDocs -- +0 first table sourceJOIN dbo.DocumentDetails row -- +1 joinON doc.ID = row.DocId AND doc.DocumentDate = row.DocumentDate -- +1 logical sequenceLEFT 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 xON row.ID = x.SourceRowId AND row.DocumentDate = x.SourceDocumentDate -- +1 logical sequenceWHERE 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 sourceGROUP 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 CumulativeTotalFROM Sales.SalesPersonWHERE TerritoryID IS NULL OR TerritoryID < 5 -- +1 logical sequenceORDER BY TerritoryID,SalesYear;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0147 : The statement complexity is 2 points above the allowed 15. | 1 | 0 |