CTE (Common Table Expressions) in SQL

CTE (Common Table Expressions) was first introduced with SQL 2005.it is nothing but a temporary result set and execution scope of a single select, insert, and update or create view. It is the best replacement for Views and exists only in the scope of running query. CTEs can be used in Stored Procedures, User Define Functions (UDFs), Triggers and Views. It is very helpful when we don't have access to create object in a database. CTE can be declared once and can be used in multiple queries. CTE can be recursive and non-recursive. It is similar to derive table that is not stored as an object. It is stored in memory.

Common Table Expression Syntax

Ø  The CTE name and it is follows the WITH keyword

Ø  The column list

Ø  The query

Example

WITH CET_Emp (FirstName,LastName,MiddleName,HireDate,BirthDate,LoginID,EmailAddress,Phone) AS ( SELECT        FirstName       ,LastName       ,MiddleName       ,HireDate       ,BirthDate       ,LoginID       ,EmailAddress       ,Phone FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee] ) select * from CET_Emp

The CTE is part of the subsequent statement only. The subsequent statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION, INTERSECT etc).

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than TempDB database. You cannot create any index on CTE.

In CTE we can’t use constraints.

Benefit of using CTE

Ø  CTE is  un-materialized/ non-index able (cannot create indexes on CTE)

Ø  CTE is logical/disposable View

Ø  CTE persists only till the very next query

Ø  CTE is mostly used for recursion, as CTE can call itself

Ø  CTE resists in memory

Ø  Reusability