Common Table Expressions ( CTE in short) is one of the new and cool feature that comes with SQL 2005. A CTE is an expression that outputs a result set, which is temporary hybrid derived table, like a temporary table. CTEs can be used for all DML statements and specially select statements.
It also provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods.
Though new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3.
Clause like Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed.
Syntax
[ WITH [ ,...n ] ]
::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
WITH OrderProducts(ProductName, OrderQuantity) AS
(
SELECT ProductName, Quantity as OrderQuantity From Orders O, Product P Where O.ProductId = P.ProductID
)
Select * From OrderProducts;
CTEs should be used in the very next statement of the declaration, they can't be used in other statements.
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: sivakumar 08 Jun 2008 | Member Level: Silver Points : 1 |
Hi
Thanks.I expect this answer.
Regards
siva
|
| Author: John Fernandez 08 Jun 2008 | Member Level: Gold Points : 1 |
Very well written Article.Thanks for sharing this information.
|