Basically, we can use it to return a particular subset of data or aggregate data across many tables, and do more manipulations on the subset. However, one might ask, it sounds like doing a subquery(See Fig 1). Well, it is true, but CTE can be encapsulated and have multiple references to it, and unlike subquery, you have choices of using CTE as non-recursive just like subquery or recursive. Best of all, it is more maintainable than subquery.
Fig 1: An example of subquery
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord
When using CTE, complex SQL statement can be made to easier to understand format that you or other developer can still follow the logic of the what it is intended to do. From years of experience, you know that your requirement is gradually more complex and in this way, the query would be better maintainable.
Temporary Table vs CTE
In a way, CTE is similar to temporary table that it can be logical separated. However, CTE is a temporary resultset that does not physically created in the Tempdb database like the temporary table does. So if exists in memory and cannot be indexed. In addition, table variable is also like the temporary table which is created in the Tempdb database.
Here is a simple example of CTE, where CTEReports is the name of your temporary resultset
;With CTEReports(EmpID, MgrID) AS
Select EmployeeID, ManagerID
Where ManagerID is NULL
Select * from CTEReports
Please note that you should always place a ";" semicolon in front of "With" statement as a good habit.
Simple Example of Recursive CTE
There are two blocks of code within the With statement called the Anchor block and Recursive block.
;WITH Numbers AS
SELECT n = 1 <------- first block
SELECT n + 1 <------- begins of second block
WHERE n+1 <= 10
select * from Numbers
The anchor block is what defined the temporary resultset, and the recursive block is the one using the resultset.
So first block has 1, and in the second block it increases by +1, and loop until it is less than or equals to 10.
So in the last select statement, you can write:
Select * From Numbers OPTION (MAXRECURSION 8) when you want to throw an error when recursion goes beyond 8th recursion.
There are many examples on the web about this topic but I hope I have used the simplest examples to explain this useful tool.