Hi

As per microsoft:

A CTE can be used to:

* Create a recursive query.

* Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

* Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

* Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

🙂