DotNet Academy of Rajesh Rolen

Solutions by Rajesh Rolen

Common Table Expressions in SQL Server (2005/2008)

Using Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

* Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
* Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
* Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
* Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.


USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Reference :

1 comments:

Anonymous February 3, 2010 at 8:48 PM  

Hey. I don't normally leave comments, but I just wanted to say thanks for the great information. I have a blog too, though
I don't write as good as you do, but if you want to check it out here it is. Thanks again and have a great day!

Shadow Priest Leveling Build

Post a Comment

About this blog

My Blog List

Advertise On This Site

Site Info

Advertise on this Site

To advertise on this site please mail on RajeshRolen@gmail.com

Information Source

About

Pages

Dot Net Academy

Advertis in This Area of Site

Powered by Blogger.

Followers

Search This Blog