How to use Common Table Expression in SQL Server
In in SQL Server 2005, the common table expression (CTE) was introduced. It is a temporary named result set that you can reference from a SELECT, INSERT, UPDATE, or DELETE statement. CTE can be used in a CREATE VIEW statement, as part of the view’s SELECT query.
There are two types of CTEs—non-recursive and recursive. First one is simple as just selecting values from a table and second one is doing a recursion on the same query based on the previously selected result set.
Syntax:
A CTE starts with ;WITH statement followed by cte name and column names selected from that query.
[WITH common_table_expression [,...]]
common_table_expression::=
cte_name [(column_name [,...])]
AS (cte_query)
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.
Example
WITH
cteSales (SalesManID, TotalSales)
AS
(
SELECT SalesManID, ROUND(SUM(Sales), 2)
FROM Sales
WHERE SalesManID IS NOT NULL
GROUP BY SalesManID
)
SELECT
sm.FirstName + ' ' + sm.LastName AS FullName,
sm.City AS City,
s.TotalSales
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.