Common Table Expressions

A CTE is a temporary result set that you can reference within another SELECT, Insert Into, T-SQL UPDATE, or T-SQL DELETE statement.

A CTE always returns a result set. They are used to simplify queries, like eliminating a derived table from the main query body:

WITH My_Table (Age, Counts)
AS
(
	SELECT Age, COUNT(Name) AS Counts
	FROM Source_Table
	WHERE Age > 55
	GROUP BY Age
)
SELECT *
FROM My_Table
ORDER BY Age
GO

No Temp Tables assignment, no need to make an intermediary table. I like to this of CTE's like Rice paddy fields where the data like the water just flows downward into the next query or CTE.

---

Children

1. Chaining
2. Recursive

---

Backlinks

• Sql Server Cte Vs Temp Table Vs Table Variable Performance Test (DevLog)
• While (DevLog)