Common Table Expression (CTE)

The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It can be assumed as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. This is similar to resulting a table in that is not stored as an object and lasts only for the interval of the query. Again it offers the advantages of higher readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks.

Use OF CTE

·         Create a recursive query.

·         It is a Substitute of a view when the general use of a view is not required. Or we can say. You do not have to store the definition in metadata.

·         Enable grouping by a column.

·         Reference the resulting table multiple times in the same statement.

Example:

With EMPCTE(Emp_Id,Address, Name, Salery) --Col. names for Temporary table

AS

(

SELECT A.Address, E.Name, E.Salery from Emp_Master A

INNER JOIN EMP_Details E ON E.Emp_Id = A.Emp_Id

)

SELECT * FROM EMPCTE  --SELECT or USE CTE temporary Table

WHERE EMPCTE.Salery > 5000

ORDER BY EMPCTE.NAME

Arguments

·         expression_name

·         column_name

·         CTE_query_definition

Guidelines for Creating and Using Common Table Expressions

• It must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.

• Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

• A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.

• 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.

• We cannot use  ORDER BY ,INTO,OPTION ,FOR BROWSE clauses in the CTE_query_definition

• When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

• Tables on remote servers can be referenced in the CTE.

• When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.