Introduction
A new feature in SQL Server 2005 is the introduction of a new virtual table (rather a ResultSet) called Common Table Expressions (CTE). CTE stores temporarily the results set during the execution of a SELECT, INSERT, UPDATE or DELETE statement. The advantage of using CTE is the ability to write recursive queries and can simplify logic by replacing the use of temporary tables or views.
Common Table Expression
A Common Table Expression (CTE) can be thought of as a temporary result set whose scope is limited to the execution session of a SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. CTEs can be used -
- To create a recursive query
- To replace view when it is not required (when it is not required to store the definition in the meta data)
- To enable grouping by a column that is derived from a scalar sub-select, or a function that is either non-deterministic or has external access
- When the resulting table is based on T-SQL variables
- When the resulting table must be referenced multiple times in the same document.
CTEs are similar to derived tables; they are not stored as objects and last only for the query session. Unlike derived tables, CTEs can be self-referencing, and can be referenced multiple times in the same query.
One of the major advantages of using CTEs is improved readability and maintainability of complex queries. The query can be broken down into separate simple logical building blocks. These simple blocks can then be used to build more complex, short-term CTEs until the final result set is generated.
CTE Structure
A CTE Structure is composed of an expression name (CTE Name), optional Column list, and a defining query. Once defined, the CTE may be referenced in the SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. The syntax for CTE is as below –
With CTE_Name (Column_Name_1, Column_Name_2 … Column_Name_n )
As
(
cte_Defining_Query
)
--Statement referencing the above CTE Structure
Select * from CTE_Name
The list of column names is optional if the defining query supplies distinct column names for all the resulting columns.
Let us take an example (the example uses the AdventureWorks database installed with the samples on SQL Server 2005) –
USE AdventureWorks;
GO
WITH Purchases_CTE (PurchasePersonID, NumberOfPurchases, MaxDate)
AS
(
SELECT POH.EmployeeID, Count(*) , Max(POH.ModifiedDate)
FROM Purchasing.PurchaseOrderHeader POH
GROUP BY EmployeeID
)
SELECT EMP.EmployeeID, EMP_CTE.NumberOfPurchases, EMP_CTE.MaxDate,
EMP.ManagerID, MGR_CTE.NumberOfPurchases, MGR_CTE.MaxDate
FROM HumanResources.Employee AS EMP
JOIN Purchases_CTE AS EMP_CTE
ON EMP.EmployeeID = EMP_CTE.PurchasePersonID
LEFT OUTER JOIN Purchases_CTE AS MGR_CTE
ON EMP.ManagerID = MGR_CTE.PurchasePersonID
ORDER BY EMP.EmployeeID;
In the above example, we have shown the basic structure of the CTE components. The CTE expression Product_CTE has three columns (PurchasePersonID, NumberOfPurchases, MaxDate) and is defined as the total number of purchase orders and the most recent Purchase Order raised for each manager in the PurchaseOrderHeader table.
In the executing statement the CTE is referenced twice, once to return the detail for the employee and the second time its for the manager. Data for both the employee and the manager is returned in a single row.
The output -
EmployeeID NumberOfPurchases MaxDate ManagerID NumberOfPurchases MaxDate
----------- ----------------- ----------------------- ----------- ----------------- -----------------------
164 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
198 160 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
223 400 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
231 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
233 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
238 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
241 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
244 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
261 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
264 360 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
266 400 2004-09-12 00:00:00.000 274 160 2004-09-10 00:00:00.000
274 160 2004-09-10 00:00:00.000 71 NULL NULL
(12 row(s) affected)
CTE Guidelines
Microsoft recommends the following guidelines when creating a CTE –
•- A CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement which references some or all of the CTE columns or can be in a CREATE VIEW statement as part of the view's defining SELECT statement.
- When multiple CTEs are defined in the same WITH clause, they must be separated by a comma.
- A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing and nesting are not allowed.
- When multiple CTEs are defined in the same WITH clause, they must be separated by a comma.
- The following clauses cannot be used in the CTE_defining_query:
? COMPUTE or COMPUTE BY
? ORDER BY (except when a TOP clause is specified)
? INTO
? OPTION clause with query hints
? FOR XML
? FOR BROWSE
- When a CTE is used in a statement that is part of a batch, the preceding statement must be followed by a semicolon.
- A query referencing a CTE may be used to define a cursor.
- Tables on remote servers may be referenced in the CTE.
- In a recursive CTE, the defining query must contain at least two SELECT statements combined by a UNION ALL set operator
Summary
The new feature in SQL Server 2005 is opening up doors for lot of opportunity for the developers to do many things easily.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|