CTE stands for Common Table Expression. It is introduced in sql server 2005.
Actualy it is not a terrible thing as we heard. We can consider it as inline view or temporary table. CTE allows us to do exact same things like what we doing using views.
- Breakdown complex queries
- Avoid sub queires
- Simply certain syntax
Syntax for CTE
WITH <cte name>[(columns)] AS( <SELECT statement> )
WITH SalesData(TotalSold,Year,Branch) AS( SELECT SUM(Total) AS 'TotalSold', YEAR(OrderDate) AS '[Year]', b.BranchName FROM Order o INNER JOIN Branch b ON o.branchId = b.branchId GROUP BY YEAR(OrderDate),b.BranchName )
Then we can do any operations to SalesData CTE as we doing to a View.
Like : SELECT * FROM SalesData
Note: CTE column specifying is optional . if not specified it takes select query result columns
We can use it for short hand for sub-query. More over there are many other benefits in CTE. I noted a here very basic usage.