Day: October 15, 2015

CTE in sql server

jeevanLeave a comment

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.

Benefits:

  • Breakdown complex queries
  • Avoid sub queires
  • Simply certain syntax

Syntax for CTE


WITH <cte name>[(columns)]

AS(

<SELECT statement>

)

Example

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.