Click or drag to resize

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

The topic describes the SA0170 analysis rule.

Message

It is recommend to not use CTE unless it is need for Hierarchical data

Description

The rule checks for common table expressions, which are not recursive and used for retrieving hierarchical data.

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.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0170 : It is recommend to not use CTE unless it is need for Hierarchial data.90
See Also

Other Resources