SA0170 : It is recommend to not use CTE unless it is need for hierarchical data

Introduction

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:

WITH CTE_Example AS (
    SELECT * FROM Employees
)
SELECT * 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:

-- Original problematic query using a CTE
WITH CTE_Example AS (
    SELECT * FROM Employees
)
SELECT * FROM CTE_Example;

-- Revised query without a CTE
SELECT * 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
),
Number5 AS (
    select n=5
)
SELECT n
FROM Numbers


;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

---

;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
UNION ALL
SELECT n + 1
FROM Numbers1
WHERE n+1 <= 10
),
Numbers1 AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Analysis Results

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

Analysis Rules