Use of CTE or Common Table Expression in SQL Server.

We can think a Common Table Expression or CTE as a temporary table. A CTE is just like a derived table because CTE is not stored as an object and exists only for the duration of the query. In other words, a CTE is similar to VIEW also. 

We can define or create a CTE as an expression, optional column list and a query. We have to specify WITH keyword and Semicolon (;) as prefix but Semicolon is optional.

Syntax:

With CTE_As_Table(col1,col2....)  AS (Select Statement)

Here, CTE_As_Table will be treated as Table name as:

Select * From CTE_As_Table;