When crafting a query in SQL, there are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example, the database for an eCommerce web application would have the standard tables - Products, Customers, Orders, OrderDetails, and so on - but we may need to run reports on a particular subset of the data or against aggregate data across these tables. Or the reporting queries we need might need to group or filter by results returned by scalar subqueries. Typically, views are used to break down complex queries into digestible chunks or to provide scalar subquery results that can be grouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-level. If we only need to reference this complex query in a single stored procedure or UDF, another option is to use a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for each use in a statement.
Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. In this article we'll examine the benefits, uses, and syntax of both recursive and non-recursive CTEs. Read on to learn more!
Read Common Table Expressions (CTE) in SQL Server 2005