What is Common Table Expression(CTE) in SQL Server?

There are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example say InvoiceDatabase where we may have tables like Order, OrderDtail, InvoiceMaster, InvoiceDetail but we may need to run report against aggregate data across these tables.

Typically, views are used to break down complex queries into desired set of data but again views are parmanent database objects with will occupy the database space. Then you are left with #temp table option, here also you need to write the whole create table then you need to insert record to the temporary table...

The CTE is a form of the derived table that can be declared once and referenced multiple times in a query.

A Simple Common Table Expression Example
  TempCTE(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )

select  * from Temp CTE

StreamName  SubStreamName
12th Science  B
12th Science  A
12th Science  AB
12th General  General
12th General  Home Science


Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

NOTE: The query using the CTE must be the first query appearing after the CTE. That you can not do like the below:

with abc(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )select * from StreamMasterselect * from abc

Post Reference: Vikram Aristocratic Elfin Share
This query creates a CTE named  TempCTE that returns top 5 StreamName with there sub-stream name. Now you can query the TempCTE to get the resultant data.
with