Hi,
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.
please follow the link below for an article of CTE:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/[
^]
-AK
Submit an article or tip