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

 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