Common Table Expressions

Microsoft offers the following four advantages of CTEs:

• 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 sub select, 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. 

• About are as follow

A common table expression (CTE) can be thought of as a temporary result set that is defined 

Difference Between STUFF Vs REPLACE

STUFF - Delete a specified length of characters and insert
another set of characters at a specified starting point.

ex: SELECT STUFF('abcdefghi', 3, 3, 'ABC')
Go
Answer is: abABCfghi

REPLACE - Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE('Abhay', 'a', 'v')
Answer is: vbhavy