A CTE starts with ;WITH statement followed by cte name and column names selected from that query.
[WITH common_table_expression [,...]]
cte_name [(column_name [,...])]
How to use the CTE:
After creating a CTE you can just refer its name as you refer a temprory table. This CTE is available only on the scope of the execution of the current query. So you will not be able to reference it out of the query.
cteSales (SalesManID, TotalSales)
SELECT SalesManID, ROUND(SUM(Sales), 2)
WHERE SalesManID IS NOT NULL
GROUP BY SalesManID
sm.FirstName + ' ' + sm.LastName AS FullName,
sm.City AS City,
FROM SalesMan AS sm
INNER JOIN cteSales AS s
ON sm.SalesManID = s.SalesManID
ORDER BY s.TotalSales DESC
In the above CTE, i have two columns selected SalesManId and total sales of him. Later in the output select query I am inner joining that CTE cteSales to SalesMan table to get the Sales man full name, city and his total sales.
So wherever you were using a view to get temperory data you can replace them with a CTE thus removes lots of Views from your database.