From this post How to use ROW_NUMBER in the following procedure?
There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem.
Now then, what is the advantage of using a CTE (Common Table Expression) over a ‘sub-query`(thus, more readable what the query is actually doing)
The only advantage of using a CTE over sub-select is that I can actually name the sub-query. Are there any other differences between those two when a CTE is used as a simple (non-recursive) CTE?
Advertisement
Answer
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full).
In general; A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures.