CTE(Common Table Expression)

CTE(Common Table Expressions) : 
                                                            CTEs are used to make the query more readable. CTE is a named  temporary result set that can be referenced within a select, insert,update,delete statement.
 CTE starts with the with clause.  CTE is defined by placing with clause before the insert,update, delete statement.
Here is the Syntax:

<expression name>  is the CTE  name which can be any valid identifier. It can be same as that of the base object name, but should be different from the other CTE name within same with clause.

The number of <column name> should be equal to the number of columns in the result set of  the <cte-defination-query>. If the column names in  <cte-defination-query> are identical then <column names> can be omitted.
 SQL SERVER supports two types of CTE
1.NON-RECURSIVE

2.RECURSIVE

1. NON-RECURSIVE:-  
A nonrecursive CTE is one that does not reference to itself within the CTE. These are simpler than recursive CTEs

e.g.

is a sample cte query on AdventureWorks database which inbuilt in sqlserver. 

2.   RECURSIVE CTE:-

                                         A recursive CTE is a CTE which reference to itself in the CTE.

e.g.

In the example we will write a CTE for printing counting from 1-10.

  We define a CTE NumberSequence which calls itself inside the definition. 

and the result will be: