CTE in sql server

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)]


<SELECT statement>



WITH SalesData(TotalSold,Year,Branch)
SELECT SUM(Total) AS 'TotalSold',
       YEAR(OrderDate) AS '[Year]',
 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s