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.

Description

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:

SQL
Copy
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.

How to fix

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:

  1. 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…).

  2. Rewrite the query to perform a direct selection from the relevant tables without using a CTE. This reduces complexity and improves performance.

  3. Review and test the new query to ensure it achieves the same results as the original while maintaining optimal performance.

For example:

SQL
Copy
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;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
Analysis Results
  Message Line Column
1 SA0170 : It is recommend to not use CTE unless it is need for Hierarchial data. 9 0
See Also

Other Resources

Analysis Rules