SA0170 : It is recommend to not use CTE unless it is need for hierarchical data |
![]() |
Misusing CTE-s in SQL queries, especially for non-hierarchical data, can lead to unnecessary complexity and performance degradation.

Common Table Expressions (CTEs) can be a useful tool in T-SQL, but when used incorrectly, they may lead to performance issues or convoluted queries. Misusing CTEs can result in situations where they are not necessary, particularly when working with non-hierarchical data. In SQL Server, if you include a CTE for a simple data retrieval task, it can introduce unnecessary complexity and degradation of performance.
For example:
1WITH CTE_Example AS ( 2 SELECT * FROM Employees 3) 4SELECT * FROM CTE_Example;
This query is problematic because it introduces a CTE when a direct query would suffice. Using a CTE here does not add any value and can make the query harder to read and maintain, while potentially impacting performance.
-
Using unnecessary CTEs can lead to inefficient execution plans, especially if they are not optimized by the SQL Server query optimizer.
-
CTEs can make query debugging and comprehension more difficult, as they can obfuscate the logic of the SQL statements.

To resolve performance issues associated with the misuse of Common Table Expressions (CTEs) in SQL queries, rework the query to eliminate non-recursive CTEs, especially for non-hierarchical data retrieval.
Follow these steps to address the issue:
-
Identify any non-recursive CTEs in your queries that are used for simple data retrieval. For example, locate queries written as WITH CTEName AS (SELECT…).
-
Rewrite the query to perform a direct selection from the relevant tables without using a CTE. This reduces complexity and improves performance.
-
Review and test the new query to ensure it achieves the same results as the original while maintaining optimal performance.
For example:
1-- Original problematic query using a CTE 2WITH CTE_Example AS ( 3 SELECT * FROM Employees 4) 5SELECT * FROM CTE_Example; 6 7-- Revised query without a CTE 8SELECT * FROM Employees;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Code Smells

There is no additional info for this rule.

SQL
1;WITH Numbers AS 2( 3 SELECT n = 1 4 UNION ALL 5 SELECT n + 1 6 FROM Numbers 7 WHERE n+1 <= 10 8), 9Number5 AS ( 10 select n=5 11) 12SELECT n 13FROM Numbers 14 15 16;WITH Numbers AS 17( 18 SELECT n = 1 19 UNION ALL 20 SELECT n + 1 21 FROM Numbers 22 WHERE n+1 <= 10 23) 24SELECT n 25FROM Numbers 26 27--- 28 29;WITH Numbers AS 30( 31SELECT n = 1 32UNION ALL 33SELECT n + 1 34FROM Numbers 35WHERE n+1 <= 10 36UNION ALL 37SELECT n + 1 38FROM Numbers1 39WHERE n+1 <= 10 40), 41Numbers1 AS 42( 43SELECT n = 1 44UNION ALL 45SELECT n + 1 46FROM Numbers 47WHERE n+1 <= 10 48) 49SELECT n 50FROM Numbers |

Message | Line | Column | |
---|---|---|---|
1 | SA0170 : It is recommend to not use CTE unless it is need for Hierarchial data. | 9 | 0 |
