What is CTE (Common table Expression).

What is Common Table Expression:

Ans:

A CTE (Common Table Expression) defines a temporary result set which you can then use in a SELECT statement.  It becomes a convenient way to manage complicated queries.


Syntax:

With expression_name (columnname1, columnname2)
(
Cte query defination
)
Select columnname from expression_name

Example:

WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
        JobTitle
 FROM   HumanResources.Employee)
SELECT EmployeeNumber,
       Title
FROM   Employee_CTE


Advantage:

Can be used to create a recursive query.
Can be substituted for a view.
Allow grouping by a column which might be derived from a scalar subset.
Can reference itself multiple times.


DisAdvantage:

The Recursive member can refer to the CTE only once.

Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

Table Variables and CTE’s cannot be passed as parameters in stored procedures.




Difference between CTE and View

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive