SA0170 : It is recommend to not use CTE unless it is need for hierarchical data |
 |
The topic describes the SA0170 analysis rule.
MessageIt is recommend to not use CTE unless it is need for hierarchical data
DescriptionThe rule checks for common table expressions, which are not recursive and used for retrieving hierarchical data.
How to fixRewrite the query not to use non recursive CTE.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
ParametersRule has no parameters.
RemarksThe rule does not need Analysis Context or SQL Connection.
Effort To Fix8 minutes per issue.
CategoriesDesign Rules, Code Smells
Additional InformationThere is no additional info for this rule.
Example Test SQL|
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 |
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