A common table expression (CTE) can be thought of as a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table. Unlike a derived table, a CTE can be self-referencing, not stored as object and can be referenced multiple times in the same query.
It can be recursive and non-recursive.
It provides the significant advantage of being able to reference itself because earlier version sql server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive step.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
Source: http://msdn.microsoft.com/en-u | Asked In: Many Interviews |